declare @dt datetime
set @dt = getdate()
print 'Now'
print @dt
print ''
print 'Today - no time'
print dateadd(D, 0, datediff(D, 0, @dt))
print ''
print 'Today – 23:59:59.997'
print dateadd(ms,-3, dateadd(D, datediff(D, 0, @dt),1))
print ''
print 'Start of this week - Starts on MONDAY'
print dateadd(wk,datediff(wk,0, @dt),0)
print ''
print 'End of this week - Starts on MONDAY 23:59:59.997'
print dateadd(ms,-3, dateadd(wk,datediff(wk,0,@dt),7))
print ''
print 'Start of this week - Starts on SUNDAY'
print dateadd(wk,datediff(wk,0,getdate()),-1)
print ''
print 'End of this week - Starts on SUNDAY 23:59:59.997'
print dateadd(ms,-3,dateadd(wk,datediff(wk,0,@dt),5))
print ''
print 'Start of Month'
print dateadd(mm,datediff(mm,0,@dt),0)
print ''
print 'Last day of current Month - 23:59:59.997'
print dateadd(ms,-3, dateadd(mm,datediff(mm,0,@dt)+1,0))
print ''
Here is a simple break down to how these snippets actually work
print 'Today - no time'
print dateadd(D, 0, datediff(D, 0, @dt))
print ''
First off we return the number of days between the date/time passed and 0 so we get the number of whole days (we have lost the time)
Today is July 14, 2009 (as an example) which using this code
print datediff(D, 0, '2009-07-14 03:34:21')
This will return 40006
We then add 40006 days to 0 and this returns the date 2009-07-14.
print dateadd(D, 0, 40006)
Jul 14 2009 12:00AM
So adding it together we get
print dateadd(D, 0, datediff(D, 0, @dt))
You will notice that some of the functions have a –3 as part of the code, this is because we want to return the last actual time that SQL Server can reference on a particular day. SQL Server can store the maximum time as 23:59:59.997. That is because SQL Server is only accurate to 3 milliseconds. If we used 23:59:59.999 it would actually roll over to the next day.
So if we were wanting to return the last time for today’s date/time we can use the following
print DATEADD(ms, - 3, DATEADD(D, DATEDIFF(D, 0, GETDATE()), 1))