Simplest way to create a date that is the first day of the month, given another date

Question

In SQL Server what is the simplest/cleanest way to make a datetime representing the first of the month based on another datetime? eg I have a variable or column with 3-Mar-2005 14:23 and I want to get 1-Mar-2005 00:00 (as a datetime, not as varchar)

Answer 1

SELECT DATEADD(mm, DATEDIFF(mm, 0, @date), 0)

Answer 2

Select DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)

To run this on a column, replace GetDate() with your column name.

The trick to this code is with DateDiff. DateDiff returns an integer. The second parameter (the 0) represents the 0 date in SQL Server, which is Jan 1, 1900. So, the datediff calculates the integer number of months since Jan 1, 1900, then adds that number of months to Jan 1, 1900. The net effect is removing the day (and time) portion of a datetime value.

Answer 3

Something like this would work....

UPDATE YOUR_TABLE
SET NewColumn = DATEADD(day, (DATEPART(day, OldColumn) -1)*-1, OldColumn)