Manipulating dates - get first day of current month, year etc

It's often useful to be able to return the first of the current year, month, or week, or even to strip off the time portion of a DATETIME field. One way to do this is to work out the difference between the current date and the "start of time", in the units of time you're interested in, and then add this many units of time to the "start of time".

The "start of time" in SQL Server is 1st Jan 1900. The following query returns 1st Jan 1900:

declare @dt datetime
set @dt = 0
select @dt 

To get the date and time right now, you could use:

select getdate()   

Then, to get the number of years between the "start of time" and now, you could use the following:

select datediff(year,0,getdate()) 

The query above basically says "what is the difference (datediff), in whole years (year), between 1st Jan 1900 (0) and today's date (getdate())". You can then add this number of whole years to 1st Jan 1900, to give you the 1st of Jan this year, by using the DATEADD function:

select dateadd(year,datediff(year,0,getdate()),0)

The query above can be adapted to get the first of the current month, week, or to strip off the time portion of today's date, by substituting "year" for "week" or "day" respectively:

select dateadd(week,datediff(week,0,getdate()),0)
select dateadd(day,datediff(day,0,getdate()),0)

To get the first of next month, you can find the first of this month, and add an extra month, as follows (note the "+1"):

select dateadd(month,datediff(month,0,getdate())+1,0)

Last day of this month:

select dateadd(day,-1,dateadd(month,datediff(month,0,getdate())+1,0))

Last day of last year:

select dateadd(day,-1,dateadd(year,datediff(year,0,getdate()),0))