Why are these two dates not equal?


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. 



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 


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.

Junior Contributor

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:


Much more elegant, thanks again!