DateTime Manipulation

The date time data type and the date time functions within SQL are things that I see coming up time and time again in news groups and forums. Questions on how to get rid of the time, how to get the first day of the week, the last day of the month and so on. With the new Date and Time data types coming in SQL 2008, things will get easier, nut until then we have to do things the hard way.

In systems I’ve worked on I’ve seen several implementations of functions to find the first and last day of a week, a month or a quarter. Some have worked well, some have worked and others, well, haven’t

So, here are some of the date functions that I use. The times are set so that these can be used in between statements and not pick up entries for the next interval.

DECLARE @TheDate DATETIME
SET @TheDate = GetDate()

/* beginning of the day */
select dateadd(dd, datediff(dd,0, @TheDate),0)
/* end of the day*/
select dateadd(ms,-3,dateadd(dd, datediff(dd,0, @TheDate)+1,0))

/* beginning of the week. Beware DATEFIRST settings.*/
select dateadd(ww, datediff(ww,0, @TheDate),0)
/* end of the week */
select dateadd(ms,-3,dateadd(ww, datediff(ww,0, @TheDate)+1,0))

/* beginning of the month */
select dateadd(mm, datediff(mm,0, @TheDate),0)
/* end of the month. Does account for leap years*/
select dateadd(ms,-3,dateadd(mm, datediff(mm,0, @TheDate)+1,0))

/* beginning of the quarter*/
select dateadd(qq, datediff(qq,0, @TheDate),0)
/* end of the quarter */
select dateadd(ms,-3,dateadd(qq, datediff(qq,0, @TheDate)+1,0))

/* beginning of the year */
select dateadd(yy, datediff(yy,0, @TheDate),0)
/* end of the year */
select dateadd(ms,-3,dateadd(yy, datediff(yy,0, @TheDate)+1,0))

For ease, these can be created as UDFs.

-- The day, without the time
CREATE FUNCTION dbo.DayStarts(@TheDate DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN dateadd(dd, datediff(dd,0, @TheDate),0)
END
GO

-- The day, 3 ms to midnight
CREATE FUNCTION dbo.DayEnds(@TheDate DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN dateadd(ms,-3,dateadd(dd, datediff(dd,0, @TheDate)+1,0))
END
GO

-- First day of the week
CREATE FUNCTION dbo.DateWeekStarts(@TheDate DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN dateadd(ww, datediff(ww,0, @TheDate),0)
END
GO

-- Last day of the week, 3 ms to midnight
CREATE FUNCTION dbo.DateWeekEnds(@TheDate DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN dateadd(ms,-3,dateadd(ww, datediff(ww,0, @TheDate)+1,0))
END
GO

-- First day of the month
CREATE FUNCTION dbo.DateMonthStarts(@TheDate DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN dateadd(mm, datediff(mm,0, @TheDate),0)
END
GO

-- Last day of the month, 3 ms to midnight
CREATE FUNCTION dbo.DateMonthEnds(@TheDate DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN dateadd(ms,-3,dateadd(mm, datediff(mm,0, @TheDate)+1,0))
END
GO

-- First day of the quarter
CREATE FUNCTION dbo.DateQuarterStarts(@TheDate DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN dateadd(qq, datediff(qq,0, @TheDate),0)
END
GO

-- Last day of the quarter, 3 ms to midnight
CREATE FUNCTION dbo.DateQuarterEnds(@TheDate DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN dateadd(ms,-3,dateadd(qq, datediff(qq,0, @TheDate)+1,0))
END
GO

-- First day of the year
CREATE FUNCTION dbo.DateYearStarts(@TheDate DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN dateadd(yy, datediff(yy,0, @TheDate),0)
END
GO

-- Last day of the year, 3 ms to midnight
CREATE FUNCTION dbo.DateYearEnds(@TheDate DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN dateadd(ms,-3,dateadd(yy, datediff(yy,0, @TheDate)+1,0))
END
GO