Thursday, February 16, 2006

SQL Date Processing - First/Last Day of a month

Oren Eini posted a nice bit about SQL. It's easy to get the first or last day of a specific month and year without doing all the silly CASTing about. (slightly tweaked in LastDayOfMonth to avoid a redundant DATEADD)

CREATE FUNCTION FirstDayOfMonth(@year int, @month int)
   RETURNS DATETIME AS
BEGIN
   RETURN DATEADD(MONTH, @month - 1, DATEADD(YEAR, @year - 1900, 0))
END
 
CREATE FUNCTION LastDayOfMonth(@year int, @month int)
   RETURNS DATETIME AS 
BEGIN
   RETURN DATEADD(DAY, -1, DATEADD(MONTH, @month, DATEADD(YEAR, @year - 1900, 0)))
END

This works because 0 as a DateTime equals 01/01/1900, so subtracting 1900 from the year and one from the month gives the correct values.

[Via http://www.ayende.com/Blog/PermaLink,guid,23fefdfe-22f1-4649-8e12-f36d06cd54cc.aspx]

UPDATE: I've added a complete list of the ways to get first/last/next values for a date/week/month/quarter/year in SQL here. (I also removed the horrid syntax coloring of this post, sorry).

2 comments:

Anonymous said...

Why the black background? It makes prints of the screen nasty.

IDisposable said...

You print things? Tree killer :)

Seriously, though, I could make a print-based CSS.