Database
Fan

## 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`...

Fan

## 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: