All Forums Analytics
deva_048 8 posts Joined 04/12
31 Aug 2012
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.

ulrich 816 posts Joined 09/09
31 Aug 2012

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)

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

deva_048 8 posts Joined 04/12
31 Aug 2012

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?

 

 

 

 

 

ulrich 816 posts Joined 09/09
31 Aug 2012

don't you have access to a TD DB?

Just try - at least it worked for me

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

deva_048 8 posts Joined 04/12
31 Aug 2012

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

ulrich 816 posts Joined 09/09
03 Sep 2012

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?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

deva_048 8 posts Joined 04/12
03 Sep 2012

Thanks,

I used like below

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

its working

You must sign in to leave a comment.