SQL DATE MANIPULATION

[krufruf]

Looking for answer on how to code sql query (using IBM DB2 Version 7) where date will automatically calculate first of the month (i.e. where effective_date<'2005-03-01) Currently this date has to be manually changed each month to reflect the first day of the CURRENT month. If this query is run on 3/15/2005 - the data should still reflect only records where the effective date is <= the first day of the current month (3/1/2005 as in example above)

Been trying different options with Year(effect_date)<=year(current date) and month(effect_date)<month(current date)..but get stumped on isolating the days.

Any help would be appreciated. Thanks!

 

[GhostWolf]

You shouldn't have to worry about the days.

Try changing your selection criteria, though:

Year(Effect_Date) < Year(Current_Date) or
(Year(Effect_Date) = Year(Current_Date) and 
 Month(Effect_Date) < Month(Current_Date))

HTH

 

[BrianTyler]

The first day of the current month is:

current date - (day(current date) - 1) days


Brian