Subtracting two dates and comparing if result 365 days

Question

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?

Answer

I think based on the tags you're using Athena, so date_diff may be the answer. The official AWS documentation links to [Presto](https://prestodb.io/docs/0.172/functions/datetime.html).

Other database systems also support DATEDIFF: [MySQL](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_datediff), [MS SQL Server](https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-ver16) and [Oracle](https://docs.oracle.com/cd/E57185_01/ESBTR/mdx_datediff.html). With POSTGRES you could use DATE_PART, see http://www.sqlines.com/postgresql/how-to/datediff.

Subtracting two dates and comparing if result >365 days

相关内容