Why are these two dates not equal?

Database

Why are these two dates not equal?

I have two fields (day1 and day2) with the same value that aren't equal to each other. 

-----------------------------

SEL 

cast (snap_dt as date) as day1, 

cast (extract(year from add_months(snap_dt,1))*10000+extract(month from add_months(snap_dt,1))*100 + 1 as date)-1 as day2,

case when day1 = day2 then 1 else 0 end as chek 

 from DUMMYTABLE 

where snap_dt = '2015-11-30';

day1             day2             chek

11/30/15      11/30/15       0

-----------------------------


I want to use a logic like this to join several months of data to a field 6 months older. However because of this issue, the join condition doesn't work.

2 REPLIES
Senior Apprentice

Re: Why are these two dates not equal?

The 2nd date is 3915-11-30, you forgot to subtract 1900 from the year:

CAST ((EXTRACT(YEAR FROM ADD_MONTHS(snap_dt,1))-1900)*10000+EXTRACT(MONTH FROM ADD_MONTHS(snap_dt,1))*100 + 1 AS DATE)-1 AS day2,

In don't think you actually need this calculation, there's ADD_MONTHS/TRUNC/LAST_DAY...

Re: Why are these two dates not equal?

Thanks! I wouldn't have guessed that casting as a date adds 1900 to the year.

Also just saw your earlier posts on LAST_DAY:

LAST_DAY(ADD_MONTHS(snap_dt,-2))

Much more elegant, thanks again!