Robyn Page’s SQL Server DATE/TIME Workbench

To celebrate the seventh anniversary of Robyn Page's classic workbench, Phil brings it right up to date. What starts as a gentle introduction to the use of dates in SQL Server ends up with some unusual tricks in creating calendars and calculating Mardi Gras.

  • revised: 21th Aug 2013 by Phil Factor
  • revised: 20th Sep 2012 by Phil Factor
  • revised: 11th May 2010 by Phil Factor

Using dates, and times in SQL Server: a workbench approach

I’d like to encourage you to experiment. One never fails to come up with surprises; for example, I’d never, before writing this, considered using LIKE when searching Date fields, or using the { t '2:40'} in a stored procedure as a literal date. Likewise, I always like to see as many examples as possible in any articles on SQL Server. There is nothing like it for getting ideas going. Formal descriptions are fine for those with strange extra lumps in their brains, but I’d prefer to see clear explanations peppered with examples! If I have any general advice, it is to use the strengths of the DATETIME, DATE, DATETIME2 and DATETIMEOFFSET data types and never attempt to bypass their use, by storing dates or times in any other formats such as varchars or integers. I’ve never come across a circumstance where such a practice has provided any lasting benefit. Also, keep clearly in mind the differences between the presentation format, data-interchange formats and storage formats of dates and times. Remember also the difference between an interval of time and a date.

Contents

The Principles

Basically, there are three different ways of representing dates, and the same principles apply to other quantities to do with humanity, such as money. Dates can be represented in a human-readable form, the ‘presentation’ mode, they can be represented in their storage form, or in their data-interchange form.

We can show this easily:

These have entirely different purposes, and it helps to understand the distinction. The storage form is what is kept in the data pages, and what is stored and searched on. Each databases system uses a different format and SQL Server has several! The presentation form is what is presented to us to understand as unambiguously and quickly as possible. The data-interchange form is used to transmit a date and time between systems, languages and time-zones.

The DataTypes

The date and time DataTypes on SQL Server 2008 and 2012 – time, date, smalldatetime, datetime, datetime2 and datetimeoffset – are greatly superior to those of previous versions of SQL Server. Why? Most importantly, we now get a standard means of recording a moment in time that takes into account ‘Local time’. Were the world flat, and the sun going around it, the DateTime format would be ideal. If you need to collect, compare, and aggregate from different locations in different time-zones, then Dates and Times must be recorded in terms of the local time, together with the offset from Coordinated Universal Time (UTC) to local time. For this, the DATETIMEOFFSET data type is ideal, and it works much like DateTime.

These date and time functions illustrate what I mean but they won’t show you the storage form, just the presentation or data-interchange form.

Why 1753 for the DATETIME range? That’s when the Gregorian calendar was generally adopted. Any dates before then have a certain ambiguity. The ‘Accuracy’ of these times is nothing like the precision of the datatype, which is designed for externally-sourced scientific data. The ‘granularity’ of the times you get from the GetDate functions is around 15 Ms, but around 1 Ms for the SYS versions of the functions.

As a general rule, use datetimeoffset where you can, use Time and Date where the data really is either just a time or just a date, but where you can get away with just using ‘local time’ (rare). I can’t think of a reason for using smalldatetime, and unless you are stuck on SQL Server 2000 or 2005, you’re very unlikely to need DateTime any more. The DateTimeOffset can take ten bytes of storage, but DateTimeOffst(2) reduces this to 7, and gives you plenty of precision for commercial applications. If you have a reason to be repelled by DateTimeOffset, then use DATETIME2, which has increased precision, and the full range of dates back to 1 AD.

All these different datatypes work the same way with the date functions. all these functions, for example, pass back different ‘storage’ datatypes, but give the same ‘presentation type’ date and time as a string in the ‘European default’ format (113).

Inputting dates

A user will supply dates in a number of formats and, at some point, you will need to get it into one of the Date/Time DataTypes in the database.

SQL Server 2012 adds a whole lot of functions to convert from parts of a date (e.g. year, month, day, hour, minute, seconds, milliseconds) to a SQL Server date. These are merely conveniences since it was possible to do this anyway, though it was laborious to do so.

These functions are:

DATEFROMPARTS ( year, month, day )
Returns a date value for the year, month, and day passed as parameters.
DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
Returns a datetime2 value for the date and time that you specify, with the specified precision.
DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
# Returns a datetime value for the specified date and time.
DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
Returns a datetimeoffset value for the parts you specify, with the specified offsets and precision.
SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
Returns a smalldatetime value for the specified date and time.
TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
Returns a time value for the specified time and with the specified precision.

Implicit conversion from the presentation format of a data into a storage form can cause problems. Dates can be coerced into the Datatype by assigning string values to variables or columns, but these are usually affected by the DATEFORMAT stored for the particular language that is current. The order in which the month (m), day (d), and year (y) is written is different in other countries. US_English (mdy) is different from british (dmy). By explicitly setting the date format you can over-ride this.

You can check your current DATEFORMAT, amongst other things by using:

Any date representation based on words (e.g. febbraio, fevereiro, february) will fail in any other language that uses a different word for a given month. To see the current language settings, use:

Nations have different conventions for representing the date as a numerical string. This is why the default DATEFORMAT changes as you change the language.

SET DATEFORMAT is used to override this for the current connection. It will change the order in which you supply the day, month and year in the date as a string, from the default for your language setting. It can take one of the following strings ‘mdy’, ‘dmy’, ‘ymd’, ‘ydm’, ‘myd’, and ‘dym’ (‘ydm’ won’t work with the DATE, DATETIME2 and DATETIMEOFFSET datatypes).

Whereas if you get the DATEFORMAT wrong….

If you need dates to be understood internationally, then you need to use the data-interchange format.

DATEFORMAT has no effect if you format your dates in a standard way.

So this is the safest way to import date strings, especially when you consider that SQL Server 2008’s DATE, DateTime2 and Datetimeoffset work differently with ANSI SQL Standard strings.

Otherwise SQL Server is fairly accommodating, and will do its best to make sense of a date. All of the following return 2012-02-01 00:00:00.000

If you use the CONVERT function, you can override the DATEFORMAT by choosing the correct CONVERT style (103 is the British/French format of dd/mm/yyyy (see later for a list of all the styles)

The CONVERT function gives you a great deal of control over the import of dates in string form, since one can specify the expected format, and is probably the best way of importing dates via a data feed, if the dates aren’t in the ISO or ODBC format.

The IsDate function

The IsDate(expression) function is used for checking strings to see if they are valid dates. It is language-dependent.

ISDATE (Expression) returns 1 if the expression is a valid date (according to the language and DATEFORMAT mask) and 0 if it isn’t. The following demonstration uses ISDATE to test out the input of strings as dates.

Inputting Times

Times can be input into SQL Server just as easily. Until SQL Server 2008, there were no separate time and date types for storing only times or only dates. It was not really necessary. If only a time is specified when setting a datetime, the date is assumed to be the first of January 1900, the year of the start of the last millennium. If only a date is specified, the time defaults to Midnight. With SQL Server 2008, we now have the DATE and TIME Data-Types, which make the use of dates and times less idiosyncratic.

You can input times a different ODBC-standard way (note that the brackets are curly braces.

…which unexpectedly gives 09.40 today, rather than 9:40 on the first of January 1900! (as one might expect from the other time input examples). This is valid in a stored procedure too.

Outputting dates

Dates can be output as strings in a number of ways using the CONVERT function together with the appropriate CONVERT styles These styles are numeric codes that correspond with the most popular date formats. You get much more versatility with the CONVERT function than the CAST function.

The CONVERT styles override the setting of the DATEFORMAT but use the current language setting where the date format uses the name of the month. If you run the following code you will get a result that illustrates all the built-in formats for your particular language settings etc. , using the current date and time

294-294-robynP1.gif

Manipulating dates

Getting the current date can be done by five functions:

When extracting parts of a DateTime you have some handy functions that return integers:

DAY, MONTH, YEAR
Here we get the day, month and year as integers.
SELECT DAY(GETDATE()),MONTH(GETDATE()),YEAR(GETDATE())
The functions DAY, MONTH and YEAR are shorter than the equivalent DATEPART command, but for more general use the DATEPART function is more versatile
SELECT DATEPART(DAY,GETDATE()),DATEPART(MONTH,GETDATE()), DATEPART(YEAR,GETDATE())
These work just as well with the other date/Time data types, of course
DATEADD

DATEADD will actually add a number of years, quarters, months, weeks, days, hours, minutes, seconds, milliseconds, microseconds or nanoseconds to your specified date. The format for this, and the other date-manipulation functions is as follows:

  • year (yy or yyyy)
  • quarter (qq or q)
  • month (mm or m)
  • week (wk or ww)
  • Day (dayofyear, dy, y, day, dd, d, weekday or dw)
  • hour (hh
  • minute (mi or n),
  • second (ss or s)
  • millisecond (ms)
  • microsecond (mcs) SQL Server 2008 or above only
  • nanosecond (ns) SQL Server 2008 or above only

In these examples we compare the date with the DATEADDed date so you can see
the effect that the DATEADD is having to it

DATEDIFF

DATEDIFF returns an integer of the difference between two dates expressed in years,
quarters, months, weeks, days, hours, minutes, seconds or milliseconds, microseconds or nanoseconds (it counts the boundaries).

We will give some practical examples of its use later on in the workbench.

DATENAME

Unlike DatePart, which returns an integer, DATENAME returns a NVarchar representing the year, quarter, month, week, day of the week, day of the year, hour, minute, second or millisecond within the date. The month and weekday are given in full from the value in the sysLanguages table.

DATEPART

DATEPART returns an integer representing the part of the date requested in the 1st parameter. You can use year ((yy or yyyy), quarter (qq or q), month (mm or m), dayofyear (dy or y) day (dd or d), week (wk or ww) , weekday (dw),hour (hh), minute (mi or n), second (ss or s), or millisecond (ms) */

Formatting Dates

Examples of calculating and formatting dates:

Calculating Dates by example

We can, of course, encapsulate all this into a function that will then give us the first, second, third or fourth occurrence of any day of the week in any month of any year perfectly easily.

Date Conversions

When converting from SQL Server dates to Unix timestamps, the dates are rounded to the nearest second (Unix timestamps are only accurate to the nearest second) SQL Server date to Unix timestamp (based on seconds since standard epoch of 1/1/1970).

The newer datatypes can give some fascinating information. Here is an instant way of finding what the current time and date is, in a variety of parts of the world.

294-294-robynP2.gif

We’ve put a fuller version of this script in the downloads at the bottom of the article.

Using dates

When storing dates, always use one of the date/time data types. Do not feel tempted to use tricks such as storing the year, month or day as integers, with the idea that this will help retrieval and aggregation for reports. It never does.

if you use the DATETIMEOFFSET, you are reasonably future-proof as you store dates as the UTC date together with the offset. This means that you can do dime-and-date calculations on data, even if it has been taken from more than one time zone.

The manipulation of the date/time data types is so critical to SQL Server’s performance that it is highly optimised. indexes based on date/time data type work very well, sort properly, and allow fast partitioning on a variety of criteria such as week, month, year-to-date and so on.

If, for example, you store a list of purchases by date in a table such as PURCHASES you can find the sum for the previous week by:

This LIKE trick is of limited use and should be used with considerable caution as it uses artifice to get its results.

More complicated Date calculations

So now some more complicated stuff. Here is how you calculate Easter:

And now for something slightly different, how could one calculate the sort of calculator that appears when you need to input a date (This can be used for some types of management reports where daily sales volumes need to be tracked.)

294-img73.jpg

If you spot a mistake or an omission, or you think of another useful date calculation, please let the editor know and we’ll get the article updated.

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.

Downloads

Tags: , , , , , , , , , ,

  • 157091 views

  • Rate
    [Total: 248    Average: 4.3/5]
  • alphagrl

    awesome
    I have spent countless hours dealing with formatting of dates/times. I wish I could have found this article years ago. Very handy for reference.

  • fels

    missing results
    Excellent articles.
    Please add statements results to complement.

  • horace

    Wow
    I have never really had much of an issue with date time field in by work but the work required was always very basic.
    This article has really opened my eyes and blown me away.

    Thankyou

  • IDisposable

    More on first/last day of week/month/quarter/year/etc
    I wrote a fairly extensive blog post on getting first / last date for day, week, month, quarter, year here
    http://musingmarc.blogspot.com/2006/07/more-on-dates-and-sql.html

  • Robyn Page

    re: missing results
    Yes, we did wonder whether to add all the statements results but decided in the end that it would bulk up the code somewhat to do it in every case. Has anyone else an opinion on this? It is something we could add in future ‘workshops’

  • WebMister

    Wonderful
    I wish the BOL was set out like this. It makes it so much clearer.
    Spot on, Robyn.

  • nigelrivett

    Couple of comments
    Good article (although I haven’t read much of it)

    It mentions using the unambiguous iso format but could also mention
    yyyymmdd hh:mm:ss.mmm
    which is my preferred option – it can be used without the time whereas the iso format can’t.

    For calculating dates (start of month etc.) it is faster to use dateadd than to convert to character. Must admit I always use the convert to character unless speed is important as it is more readable.

  • two_calls

    Myth Buster
    Well, so much for the notion that Beauty and Intelligence are rarely found in such quantities in our line of work…
    GREAT FORMAT – I like to keep good examples of working cose in my Solution Explorer for reference. The obscure MS examples rarely work for my beginners level of understanding.

    Well done – Waiting for some more reference material…

  • Robyn Page

    Re: Couple of comments
    –I suspect the character conversion method may be slower
    –but I can’t prove it because there isn’t much in it
    –all date functions seem very fast.
    –here is the test harness I used. Can anyone think
    –of a better one?
    –like Nigel, I’ll continue to use the Character version as it is
    –easier to remember and pretty versatile
    –Anyone know better?

    set nocount on
    declare @ii int
    declare @start Datetime
    Declare @bucket table (theDate datetime)
    Declare @otherbucket table (theDate datetime)

    –test out getting the date ten thousand times
    –two different ways
    –You need to change the order of testing to ge a comparison
    select @ii=10000, @start=GetDate()

    while @ii>0
    begin
    insert into @bucket(theDate)
    Select convert(char(11),getdate(),113)
    select @ii=@ii-1
    end
    –report how long it took
    Select [time taken (ms) is ]=Datediff(ms,@Start,GetDate())–1453ms

    –set the timer and use the DateDiff trick
    select @ii=10000, @start=GetDate()
    while @ii>0
    begin
    insert into @otherbucket(theDate)
    Select Dateadd(dd, DateDiff(dd,0,GetDate()),0)
    select @ii=@ii-1
    end
    –report how long it took
    Select [time taken (ms) is ]=Datediff(ms,@Start,GetDate())–1423ms

  • ByrdMan

    Very, Very Cool
    Robin, you are the fliest, geekiest chick that has ever come across my path. Damn good article, as well as your SQL Backup (which has helped me tremendiously). Geez, where have you been all my life!!! Kudos 🙂

  • Robyn Page

    Birdman: very very cool
    Come on. I expect you say that to all the DBAs you meet!

  • rmallamace

    Great Work
    Why can’t all examples be this in depth?
    The only suggestion I can give for future examples is include a print statement with the comments/code – then you can see it in the resutls as well

  • Auke

    Almost perfect!
    /*
    Very good article! I bookmarked it immediately 😉
    But I think the ISO week is incorrect… 🙁
    For instance for today (oktober 25th, 2006) it returns week 44 and I’m quite sure it’s 43!
    */

    –ISO-8601 number of the week of the year (monday as the first day of the week) SET datefirst 1 SELECT DATEPART(week,GETDATE()) –you may need to preserve and restore the value

    /*
    I’ve been struggling with the whole ISO thing myself and after trying to get things ‘clean’ (as in a single line) for too long, I decided to do things the ‘easy’ way (see below).
    */

    CREATE FUNCTION
    ISOweek
    (
    @DATE datetime
    )
    RETURNS INT
    AS
    BEGIN
    DECLARE @ISOweek int
    SET @ISOweek = DATEPART(wk, @DATE) + 1 – DATEPART(wk, CAST(DATEPART(yy, @DATE) as CHAR(4)) + ‘0104’)

    –Special cases: Jan 1-3 may belong to the previous year
    IF (@ISOweek = 0)
    SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy,@DATE) – 1 AS CHAR(4)) + ’12’ +
    CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2))) + 1

    –Special case: Dec 29-31 may belong to the next year
    IF ((DATEPART(mm,@DATE) = 12) AND ((DATEPART(dd, @DATE)-DATEPART(dw, @DATE)) >= 28))
    SET @ISOweek = 1
    RETURN(@ISOweek)
    END

    /*
    Furthermore in some cases I needed to show the year as well. For instance januari 1st 2005 is in week 52 of year 2004.
    */
    CREATE FUNCTION
    ISOyyyyww
    (
    @DATE datetime
    )
    RETURNS varchar(6)
    AS
    BEGIN
    DECLARE @ISOyear int
    DECLARE @ISOweek int

    DECLARE @YEAR int
    DECLARE @MONTH int

    SET @ISOweek = dbo.ISOweek(@date)

    SET @YEAR = DATEPART(year, @date)
    SET @MONTH = DATEPART(month, @date)

    SET @ISOyear = @year
    IF @MONTH = 1 and @ISOWEEK > 50 SET @ISOyear = @YEAR – 1
    IF @MONTH = 12 and @ISOWEEK = 1 SET @ISOyear = @YEAR + 1

    RETURN convert(varchar(4), @ISOyear) + right(‘0’ + convert(varchar(2), @ISOweek), 2)
    END

    /*
    If you have a ‘cleaner’ method I’m very interested!
    */

  • jamesperry

    Great article
    A great article, i often get asked questions regarding the datetime field within SQL Server so now i can pass any confused developers this article.

  • Auke

    Better testing
    –Hi, test seems quite ok, but don’t use the tables. it will flood the tempdb and make it harder to compare (the order of testing shouldn’t matter any more).
    –Added my own function which uses the fact that datetimes are stored as a float (integer part = date, decimal part = time)
    –The datediff function seems to the fastest!

    set nocount on
    declare @ii int
    declare @start Datetime
    declare @dummy Datetime

    –test out getting the date hundred thousand times
    –three different ways
    select @ii=100000, @start=GetDate()
    while @ii>0
    begin
    set @dummy = convert(char(11),getdate(),113)
    set @ii=@ii-1
    end
    –report how long it took
    Select [time taken (ms) is ]=Datediff(ms,@Start,GetDate())–1500ms

    –set the timer and use the DateDiff trick
    select @ii=100000, @start=GetDate()
    while @ii>0
    begin
    set @dummy = Dateadd(dd, DateDiff(dd,0,GetDate()),0)
    set @ii=@ii-1
    end
    –report how long it took
    Select [time taken (ms) is ]=Datediff(ms,@Start,GetDate())–593ms

    –Set the timer and use the convert to float and floor
    select @ii=100000, @start=GetDate()
    while @ii>0
    begin
    set @dummy = convert(datetime, floor(convert(float, getdate())))
    set @ii=@ii-1
    end
    –report how long it took
    Select [time taken (ms) is ]=Datediff(ms,@Start,GetDate())–656ms

  • SAinCA

    Rats! Copy-paste into Management Studio bites!
    Admirable thoroughness and “global” examples – THANKS.

    Any chance the next workbench could use paragraph ends instead of line breaks? Copying the examples into SS2K5 MS results in just 19 lines, some over 20K characters long…

  • bisjom

    The Best !
    This is the one that i have been looking for ages..
    Really helpfull..
    Thanks Robyn!

  • Kamran

    Excellenet Work
    Excellent Work Done by Robyn.But one thing for which i was searching Datetime functions in SQL server is the last date of the month which is missing in it.
    Can you tell me how to get Last date of the month.

  • Auke

    End of month
    @Kamran

    What about start of next month minus one day? 😉

    SELECT DATEADD(d, -1, (CAST(’01 ‘+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,1,GETDATE()),113),8) AS datetime)))

  • erikb68

    exelent examples
    Just keep on doing it the way You are… Perhaps You have some thoughts on topics like “T-SQL best pratices” or “T-SQL Tuning” ?

  • iordan

    Elapsed time
    When time is to be considered more like an interval rather than a fixed point, e.g. measuring the time it takes you to write an article as good as Robin’s vs. the time you get up in the morning, the value easily can be >24 h. Then the “datetime” format is not quite useful – problems with the reference point (“Jan 1, 1900”), problems with data entry and displaying etc.

    So, I use text – char(8) with pattern “hh:mm:ss”. This can be easily converted into seconds and added to whatever base point (datetime) you choose. Is there another way?

  • rmallamace

    End of month
    The end of month query is fine if you are only storing dates, but if you are storing time as well make sure your calculation is the start of next month minus one second to capture times after 00:00:00.000

    SELECT DATEADD(s, -1, (CAST(’01 ‘+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,1,GETDATE()),113),8) AS datetime)))

  • Robyn Page

    re:elapsed time
    iordan’s approach is a good one because one can see instantly how much time has elapsed from looking at the table. However, one should, I believe, use a user-defined type with a rule, to check for valid data, and you’d have to be very careful about date calculations. I’d have to add that when I have to store time-intervals, I use an integer based on the number of seconds in the interval, and this seems to be the general consensus approach. This certainly makes date-arithmetic and interval-arithmetic a lot easier. (e.g. what is the average time I spend writing these workshops)

  • mkn

    Newbie Question
    I am starting to re-write my travel reservation system into SQL from (another) database. I set up my flight schedule file with smalldatetime containing both date and departure time. But I have just realized it’s tricky to do comparisons like “get all flights between these dates” due to time content. Can anyone advise me on the approved way to do this. For example, should I store the date in one smalldatetime field and the time in another?
    Thanks in advance, Malcolm Needham

  • Robyn Page

    Re: Newbie Question
    No, I can assure you it is a lot more difficult to do the “get all flights between these dates” if you have two columns as you are suggesting. I’d recommend you to have the date and time in one column. then you can easily select all flights between two dates by
    SELECT (flight information) from (table) where (departure time) between (earliest date) and (latest date)
    in the article, there should be all the SQL you need to extract any information from your ‘departure date/time’ column
    Good luck with the application, Malcolm!

  • Anonymous

    Awesome reference
    Robyn,

    Thank you for the wonderful reference. One thing I didn’t see and have been struggling with is returning data for work days only. This will need to exclude weekends and holidays. Any ideas?

  • Anonymous

    Wonderful Works
    Thanks for this article.
    I wonder if BOL will give more details as in this article.
    Hope to hear more from you.
    Thanks again Robyn,

  • Anonymous

    Thanks
    Thanks for the reference, is really amazing all the things that you can do with datetime information.

  • Anonymous

    Excellent Tutorial
    Better than any reference manual!

  • Anonymous

    Awesome!!
    Great work, thanks!

  • Kelly Logan

    Bookmark set.
    Excellent work, Robyn.

    Thank you for a very clear and concise page on dates and times in SQL Server queries.

    Kelly Logan

  • Anonymous

    GETDATE()
    Would reading all this increase one’s likelihood of actually getting a date with the author?

  • Arun Sabat

    Nice one
    Thank you posting this. I got lot more ideas.

  • Karen

    Date of the latest friday??
    I’m trying to pull the date of the previous Friday. Example: Today is 06/20/2007, I need to pull 06/15/2007. So no mater what day of the week, I’m trying to pull the date of the last occuring Friday. I’m thinking it will have to reside within a while statement. True?

    Thanks in advance!

  • Karen

    Date of the latest friday??
    I’m trying to pull the date of the previous Friday. Example: Today is 06/20/2007, I need to pull 06/15/2007. So no mater what day of the week, I’m trying to pull the date of the last occuring Friday. I’m thinking it will have to reside within a while statement. True?

    Thanks in advance!

  • Phil Factor

    Re Date of the latest friday??
    False!

    SELECT DATEADD(DAY,-((7-DATEPART(dw,GETDATE())+(((@@Datefirst+3)%7)+3)) % 7),GETDATE())

    That gives you the current time, last friday, whatever your DateFirst setting.

  • ArchieInUK

    “Regardless of Datefirst setting”
    I did not get any of the above code working using different datefirst setting!

    The code below will ALWAYS gives the begining of last monday with respect to getdate(), regardless of datefirst setting.

    select CONVERT(smalldatetime, CONVERT(varchar,GETDATE(), 107)) – (DATEDIFF(day, 0, GETDATE()) % 7 + 7) as ‘Last Monday’

  • Anonymous

    Date Formats
    You go techno geek girl! Awesome. Thanks

  • John

    Time Averaging
    I have an ASP page that creates and populates an HTML table from a SQL table. The SQL table contains two datetime fields (SignInTime and RepTimeIn). In the SELECT statement that populates the HTML table, I use the following code to populate a third column called “Wait Time” that is the difference between the two fields named above:

    “convert(varchar,RepTimeIn-SignInTime,108) Wait”

    This works fine. However, in another location on the same page I would like to display the average of all of these “wait times”, but I have been unable to do so. Can anyone suggest a possible solution?

  • Lucien

    Thank you
    Thank you, Love you, this is not the first time i’ve stumbled upon your name when i’ve had an sql question. Again, thanks.

  • Salman

    Thank You
    when ever i have issues with my dates
    i come here. Robyn Page Date Work Bench

  • Suresh

    Week Of Quarter
    Do we have any function to display the Week no of a Quarter.

    I dont want the week no of the year.

  • Anonymous

    WOW
    Great article! This is exactly what I needed. Thank you so much. I think I’m in love!

  • eknath.dohale@gmail.com

    Mind Blowing
    This is the Mind Blowing Article.

  • TREY PAIGE

    Thank You
    You are Awesome

  • Ermond

    Thanks for this article
    Really very helpful this article.
    Thanks for it. I needed something like this desperately

  • Mitesh Oswal

    FIND Last Thursday From Current date

    DECLARE @DATE DATETIME
    SET @DATE=’02/28/2007′
    SELECT CASE WHEN DATEPART(DW,@DATE)>3
    THEN DATEADD(DD,-(DATEPART(DW,@DATE)-4),@DATE)
    ELSE DATEADD(DD,-(DATEPART(DW,@DATE)+3),@DATE)
    END

  • Margot

    datetime format
    This has saved my sanity, thank you

  • Anonymous

    Integer convert to datetime
    I am at a very elementary level of understanding. I have integer data that I needs to be converted to datetime. So I can then format it from seconds to hours.

  • Anonymous

    good one
    Got good help from this article.
    Thanks for contribution

  • rajesh

    good for sql starters
    hey robyn …keep it up..really nice article.

  • Sayyad

    Re: Thanks
    Its really helpful for beginners and old timers as well.

    Thanks

    best regards
    Sayyad

  • brendans

    Dates, time series and multiple datasources
    For information on how to pull date/event based info from multiple data sources, see this:

    http://www.izenda.com/Site/KB/CodeSamples/Combining-Multiple-Event-Based-Fields-with-UNION

  • Robyn Page

    The workbench.
    Thanks to ‘y all for the kind comments. i keep wondering whether to do an update of this but it seems to have stood the test of time.

  • Chris Turner

    Calendar table
    Excellent article Robyn, thanks. One addition that I’ve found very useful is to create a calendar table, which contains all the dates for the next ten years. This then allows me to hold against each date the period start date, period end date, whether or not it’s a working day and much more besides. If you have a financial calendar that starts at an odd time of the year (26th of June in one case) or clients/customers that use periods that are different to your own, it allows you to hold those dates as well.

    It’s an overhead at the end of each year to extend the table for another twelve months, but the benefits it brings are definitely worth it. Oh, and all the dates in it are stored as datetimes…

    C

  • venkys

    Great Stuff
    But I had one doubt,
    How can I insert null into a date time column,
    As of today its getting inserted as 1/1/1900:00 something like that.
    Kindly suggest some alternative.

  • Robyn Page

    Re: inserting NULL into a data
    All the Date/Time datatypes can be made nullable. All you need to do is to assign a NULL to the column or variable.
    Set MyDate = NULL
    Setting it to ‘1/1/1900:00’ is a bad idea since NULL means ‘unknown’ and ‘1/1/1900:00’ means Midnight on Jan 1st 1900.
    There may be more to your question though….

  • davem

    Calendar table
    Good idea Chris…

    Do you have the sql for the calendar table creation / updates so we can all use it?

  • troyb@tulalipresort.com

    Need date period
    I need information on how to extract the last 6 months of data excluding the current month.

  • troyb@tulalipresort.com

    Need date period
    I need information on how to extract the last 6 months of data excluding the current month.

  • Lone.Watcher

    And more on Easter.
    Hello Robin, this is such an excellent article. I find my self referring back to it often, and I’ve pointed associates to it as well.
    I am curious about the logic of the SET @dy formula.
    If possible, can you talk us through that?
    Thanks.
    -Dave.

  • jt@taylormadesoft.com

    Cool Stuff Robyn
    Good stuff but you didn’t seem to answer how to create a datetime window using the sysdatetimeoffset of now and make one midnight morning and one a fraction before midnight tonight. This seems to be the trick but everywhere I looked no one had it right. See my modulo division and such. Once the understanding of the pieces and parts were there then the algorithm evolved on how to pull it out and put it together.

    DECLARE @date datetimeoffset(7) = SYSDATETIMEOFFSET();

    SELECT DATETIMEOFFSETFROMPARTS(
    DATEPART(YEAR, @date),
    DATEPART(MONTH, @date),
    DATEPART(DAY, @date),
    datepart(HOUR, @date),
    DATEPART(MINUTE, @date),
    DATEPART(SECOND, @date),
    9999999,
    DATEPART(TZOFFSET,@date) / 60,
    DATEPART(TZOFFSET,@date) % 60,
    7);

  • Phil Factor

    Re: Cool Stuff Robyn
    @jt@taylormadesoft.com

    Surely, wouldn’t it be easier to get the datetime window as being between these two DATETIMEOFFSETs

    SELECT convert(DATETIMEOFFSET(7),stuff(convert(CHAR(34),SYSDATETIMEOFFSET(),121),12,16,’ 00:00:00.0000001′)),
    convert(DATETIMEOFFSET(7),stuff(convert(CHAR(34),SYSDATETIMEOFFSET(),121),12,16,’ 23:59:59.9999999′))

  • babap

    Excellent use of Examples, love it….Suggest use SQL standard syntax
    Robyn: Your use of examples interspersed with scenarios approach is highly conducive to SQL understanding and learning. Excellent exposure to the gamut of available features. Good work. May I suggest the use of ISO SQL standard syntax where applicable e.g. CURRENT_TIMESTAMP vs. GETDATE() , CAST vs. CONVERT … 🙂

  • Feltonam

    Extract short name of the previous month in TSQL 2008
    Hi

    I am working with records dated the 1st of each month (DTTM format) and I need to extract the year as yy, also the number and the short name of the previous month as seperate outputs. I have tried several combinations of dateadd and datepart with some success but have a problem with the month number for records dated 1st January. If I do dateadd mm -1 the month predictably defaults to 00. Any help welcome.

  • Kris

    How to retrieve 1st day of February for next 100 Years?