Days between two dates (different years)

Database
Enthusiast

Days between two dates (different years)

Hello, 

cast(cast(cast(min(sale_date)as date format 'yyyy-mm-dd') as char(10)) - '2013- 11-01' as decimal(15,0)) as sale_days

the above query is working only for dates_ between in 2013. Can somebody help me fix this query to work for days between 2014 and 2013?

Thanks

6 REPLIES
Enthusiast

Re: Days between two dates (different years)

Try this..

cast(cast(min(sale_date)as date format 'yyyy-mm-dd') - cast('2013- 11-01' as date format 'yyyy-mm-dd') as decimal(15,0)) as sale_days

Enthusiast

Re: Days between two dates (different years)

Your dates are in different formats:

Try to  bring both in the same  format  say example below:

SELECT DATE '2014- 11-01' - DATE '2013- 11-01';

Enthusiast

Re: Days between two dates (different years)

 I cannot enter the date directly, I need to get that from minimum of sales_date. how can I format this date?

cast(cast(cast(min(sale_date)as date format 'yyyy-mm-dd') as char(10))

Enthusiast

Re: Days between two dates (different years)

What values you get from cast(cast(min(sale_date)as date format 'yyyy-mm-dd')) ?

Do a select of your cast date and see its value.

So you can think of bringing to the same format like :

SELECT cast(min(sale_date)as date format 'yyyy-mm-dd')) - DATE '2013- 11-01';

Enthusiast

Re: Days between two dates (different years)

That worked, Thank you.

New Member

Re: Days between two dates (different years)

Theres a typo error in your command , you are using one extra space .Please try the below command:

SyntaxEditor Code Snippet

SELECT DATE '2014-11-02' - DATE '2013-11-01';