Returning the First Day of the Month from a Given Date/Time value in SQL Server

Published 05 December 06 09:19 PM | Scott Mitchell

First off, it's been a long time since my last post, close to a month! Things have been very busy between the new batches of Working with Data in ASP.NET 2.0 tutorials, the monthly Toolbox column, other miscellaneous writings (working on an article on JSON for MSDN Online with my favorite coauthor Atif Aziz), and a couple ongoing consulting jobs. And of course the holiday season is right around the corner! Sadly, this blog falls a little low on the list of priorities. But I digress - enough excuse-making for one entry. Let's get to some technical content!

SCENARIO: Given a date/time value in SQL Server, you need to compute the first of the month in T-SQL. Perhaps you have a stored procedure and are passed in some date/time value and need to store the first of the month in a particular record. How do you do this?

SOME OPTIONS: Sadly, T-SQL doesn't have a DateSerial-like built-in function. DateSerial, in VBScript, allowed you to construct a date object by passing in the year, month, and day as three parameters. If T-SQL did have such a function, we could just do:

DATESERIAL(YEAR(dateValue), MONTH(dateValue), 1)

However, T-SQL does allow for date values to be constructed as strings, so you could append the year, a hyphen, then month, a hypen, and the string “01” to end up with a string like “2006-12-01”. So that is one option.

Another one that feels a bit less dirty is to use the DATEADD function to subtract the current date minus 1. For example, if today is ~~December 12, 2006~~ December 16, 2006, to get the first of the month take the current date and subtract the day (16) minus 1. That is, subtract 15 days. Voila, you're now at December 1, 2006! This can be accomplished with the following T-SQL statement:

DATEADD(dd, -(DAY(dateValue) - 1), dateValue)

Computing the first of the month from a given date/time value is also useful if you need to compute the last day of the previous month. This can be computed by subtracting one day from the first of the month (simply omit the -1 in the above statement).

For more T-SQL date/time-related recipes check out Date and Time Manipulation in SQL Server 2000 by Manuj Bahl.