Re: An easy (I think) SQL question

While there are excellent comments about:
- converting the date inputted into a number vs converting the number in
the file to a date (for performance reasons)
- using iDate so that conversion is easy, AND, had error trapping for bad
dates
there is little talk about taking a date input (such as current date) and
converting that to the first day of the month, from 3 months ago.
IBM provides a LAST_DAY function but does not provide a FIRST_DAY
function. Yes, everyone knows that the first day of the month is 1. But
whip up a 'simple' sql function which returns the date of the first day of
the month from 3 months ago.

values date('2016-05-31') - 3 month
returns '2016-02-29'
Try to get it to return '2016-02-01'

he also wants to make sure that end of year is covered, such as
values date('2016-03-31') - 4 month
returns '2015-11-30'

Notice that I also covered those months which also have less days than 31?
And a month with a leap day in it?

Which is often a good reason to stand clear of home grown conversion
routines.


Rob Berendt