Simplest way to create a date that is the first day of the month, given another date
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.
Something like this would work....
UPDATE YOUR_TABLESET NewColumn = DATEADD(day, (DATEPART(day, OldColumn) -1)*-1, OldColumn)