Nearly a month ago I made a statement about date processing in SQL that made me use SQL CLR in order to solve this problem. Since then I think I learned a couple of things about SQL, so I think I would like to retrack this statement.

The issue that I had was finding out the first day of a month given a spesific year / month combination. I eventually solved it with something similar to:

new DateTime(year,month,1)

The problem was that it wasn't handling null values very well, and I didn't feel like loading VS.Net, adding nullables and checks to the whole thing. One of the things that really annoyed me with SQL is that there isn't a straight forward way to take a day, month, year combination and make a date out of it. In .Net it's very easy to do, but I couldn't figure out to do this in SQL.

Anatoly tried to point me in the right way, but I couldn't see it. Anyway, today it hit me, and I immedately dropped my SQL CLR function in favor of this one:

CREATE FUNCTION FirstDayOfMonth(@year int, @month int) 

RETURNS DATETIME AS  

BEGIN 

      RETURN DATEADD(MONTH,@month-1,DATEADD(YEAR,@year-1900,0)) 

END

This handles everything just fine, uses T-SQL (so no security issues), and it's actually readable to me. I took a second look at Anatoly's code, and it is just as clear to me now. A month and a half ago I need to really think about it to figure it out, and even then I rejected it in favor of the familiar CLR based solution.

The secret above is with the zero, which translate to 01/01/1900, once I've a known date, I can start playing with it. My second issue was with finding the last day of the month, once I had the above function, it was simplicity itself to write the next one:

CREATE FUNCTION LastDayOfMonth(@year int, @month int) 

RETURNS DATETIME AS  

BEGIN 

      RETURN DATEADD(DAY,-1,

            DATEADD(MONTH,1,

                  DATEADD(MONTH,@month-1,

                        DATEADD(YEAR,@year-1900,0))))

END

Get the first day of the month, add a month and substruct a day. Very nice to do.

Next step, figuring out how to strip date / time components of a date.