Need to calculate days

Analytics
Enthusiast

Need to calculate days

Date Table:

Table name:control_date

cols: Processdate

For E.g)

Processing date will have 8/30/2012

Query: (Its failed for invalid date)

select processdate + (INTERVAL '1' DAY) - (INTERVAL '26' MONTH) FROM CONTROL_DATE;

Actual OUTPUT:

6/31/2012 (We don't have 31st for June month)

Expected output:

7/1/2010

My question is:

Using above logic how can we get the expected output. Can we use the days instead of month in the query. If so, please provide the sql.

Help me on this.

Thanks in advance.

6 REPLIES
Supporter

Re: Need to calculate days

Why do you expect 7/1/2010 if you go back 26 month from 8/31/2012?

I would expect 6/30/2012

which you get via


select cast('2012-08-30' as date) as base_dt,

       add_months(base_dt+1,-26)

Enthusiast

Re: Need to calculate days

07/01/2010 which is posted above is wrong it's my mistake. And one thing is, the sql you mentioned above the same logic i tried but its looking for 06/31/2012. will the above mentioned SQL will works?

Supporter

Re: Need to calculate days

don't you have access to a TD DB?

Just try - at least it worked for me

Enthusiast

Re: Need to calculate days

Thank you so much. I got it....

Supporter

Re: Need to calculate days

X0.EVENT_START_DATE > ((X2.processing_date as date) as bs_date, add_months (bs_date+1,-26))

assume X0.EVENT_START_DATE is a date 

> would require that the right hand side is also a date

But what is 

 ((X2.processing_date as date) as bs_date, add_months (bs_date+1,-26))?

The outer () is covering two expressions

(X2.processing_date as date) -> missing cast???

as bs_date -> wrong in comparisons

so what should 

 ((X2.processing_date as date) as bs_date, add_months (bs_date+1,-26)) be?

Enthusiast

Re: Need to calculate days

Thanks,

I used like below

and x0.event_start_date >(add_months(x2.processing_date+1,-26));

its working