By using AWS re:Post, you agree to the Terms of Use
/Subtracting two dates and comparing if result >365 days/

Subtracting two dates and comparing if result >365 days

0

I have this sql SELECT instrument_id, maturity_dt, issue_dt, (maturity_dt - issue_dt) as days FROM instrument_desc_current where maturity_dt is not null and issue_dt is not null and (maturity_dt - issue_dt) > 365 limit 100 but get this error... SYNTAX_ERROR: line 5:30: '>' cannot be applied to interval day to second, integer commenting out "and (maturity_dt - issue_dt) > 365 my result set looks like... instrument_id maturity_dt issue_dt days 1000244483 3/22/2011 3/21/2011 1 00:00:00.000 1000244596 7/1/2020 4/4/2000 7393 00:00:00.000 1000244665 4/1/2019 12/8/2011 2671 00:00:00.000 1000245009 6/1/2021 6/1/2011 3653 00:00:00.000 1000245547 5/23/2011 4/6/2011 47 00:00:00.000 1000245766 11/8/2012 11/7/2012 1 00:00:00.000

How do I select only those records which are greater than 365 days?

asked a month ago21 views
1 Answers
0

I think based on the tags you're using Athena, so date_diff may be the answer. The official AWS documentation links to Presto.

Other database systems also support DATEDIFF: MySQL, MS SQL Server and Oracle. With POSTGRES you could use DATE_PART, see http://www.sqlines.com/postgresql/how-to/datediff.

answered a month ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions