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

 

4 Comments

  1. Bob H

    Gail, why do you need to know 3 ms to whatever?

    Is it for use with a BETWEEN?

    Reply
  2. Gail

    It is indeed for BETWEEN. As I’m sure you know, BETWEEN is inclusive on both bounds. So
    BETWEEN @SomeDate and DATEADD(dd,1,@SomeDate)
    will include midnight of both days, which may not be what’s wanted.
    To use BETWEEN and not get midnight on the other side, remove 3 ms from the end date as that’s the smallest granularity of SQL’s DATETIME (NB, not the newer SQL 2008 date types though)

    So BETWEEN @SomeDate and DATEADD(ms,-3,DATEADD(dd,1,@SomeDate))
    Will get you midnight of the starting date, but not the ending.

    The other way is not to use BETWEEN at all, but to use inequalities
    WHERE SomeColumn >= @SomeDate and SomeColumn < DATEADD(dd,1,@SomeDate)

    Reply
  3. Mark

    I’m trying to update a query (and it’s a query only) that will run from the 15th of the prior month to the 14th of the current month and have the date automatically update depending on the date the query is actually run. Date format is YYYY-MM-DD. Format is DATETIME. Thanks!

    Reply
  4. Gail

    Mark, I suggest that you post the question on one of the SQL forums. SQLServerCentral, SQLTeam or the MSDN forums. A blog comment is not the best place to ask questions unrelated to the post.

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *