Some date functions - just date no time, last day of month, first day of month, first day of week and last day of week
The following are a set of scripts to return specific dates relative to the
current date.
The trick is to get dates with no time, that's easy in SQL 2008 because you
can do cast(sysdatetime() as date)
Annoyingly there is no "System
function to return just the date and no time" as i've blogged here
so some of the functions use tricks to get just the date.
--How to get just the date
select convert(datetime,convert(char(8),getdate(),112),112)
select dateadd(day,0,datediff(day,0, getdate()))
--SQL 2008
select cast(getdate() as date)
--last day of the month
select dateadd(day,-1,dateadd(month,datediff(month,0, getdate()+1),0))
--SQL 2008
select dateadd(d,-day(sysdatetime()), cast(sysdatetime() as date))
--first day of this month
select dateadd(month,datediff(month,0, getdate()+1),0)
--SQL 2008
select dateadd(d,-day(sysdatetime())+1, cast(sysdatetime() as date))
--first day of the week
SET DATEFIRST 1 --1 is
monday
select dateadd(week,datediff(week,0, getdate()),0)
-datepart(weekday,0)+1
--SQL 2008
select dateadd(WEEKDAY ,-datepart(weekday,sysdatetime())+1, cast(sysdatetime() as date))
--last day of week
SET DATEFIRST 1 --1 is
monday
select dateadd(week,datediff(week,0, getdate()),0)+
7-datepart(weekday,0)
--SQL 2008
select dateadd(WEEKDAY ,7-datepart(weekday,sysdatetime()), cast(sysdatetime() as date))
-
