3

I have some doubts about SET DATEFORMAT command.

1)Does this command is only used for inserting and updating date format and it does not effect the date format of that column? OR does this effect my table after inserting values too?

2) I tried using below command(for date datatype) and got error like This session's YDM date format is not supported when converting from this character string format to date, time, datetime2 or datetimeoffset. Change the session's date format or provide a style to the explicit conversion. . why the error comes for date datatype(where as for smalldatetime it works as shown in link)?

CREATE TABLE #tempTable (DateFormatSample DATE)
SET DATEFORMAT MDY
INSERT INTO #tempTable
VALUES ('09/28/2007')
SET DATEFORMAT YDM
INSERT INTO #tempTable
VALUES ('2007/28/09')
SET DATEFORMAT YMD
INSERT INTO #tempTable
VALUES ('2007/08/28')
SELECT DateFormatSample
FROM #tempTable
DROP TABLE #tempTable

3)It was mentioned like "The setting of SET DATEFORMAT is set at execute or run time and not at parse time." What does it mean?

  • You should check the official MSDN page about SET DATEFORMAT and not trust any "SQL authority". – ypercubeᵀᴹ Dec 10 '14 at 12:00
  • @ypercube Yes. First i checked MSDN itself, but i was not clear about it. – IT researcher Dec 10 '14 at 12:01
  • That website focuses on public relations and SEO rather than technical accuracy. I'm sure you'll get an answer here. – ypercubeᵀᴹ Dec 10 '14 at 12:03
  • 1
    @ypercube Ok. But msdn has no information about what i have asked.I wanted detail answers like thomas kejser mentioned below. – IT researcher Dec 10 '14 at 12:04
  • @ITresearcher: Thanks for the kind words. Please mark my answer as approved if it provides the detail you need. – Thomas Kejser Dec 10 '14 at 12:13
6

1) SET DATEFORMAT has no effect on the actual storage of the table. It is only used to format output and interpret strings. Behind the scenes, all the date formats are stored as integers in a canonical format.

The actual representation of the data type depends on which type is used. For example, SMALLDATETIME is an integer that stores the number of seconds since 1900-01-01. Some of this bubbles to the surface when you cast like this:

SELECT CAST(0 AS SMALLDATETIME)

Result: 1900-01-01 00:00:00

Similarly, for DATETIME:

SELECT CAST(0 AS DATETIME)

Result: 1900-01-01 00:00:00.000

Interestingly, this fails:

SELECT CAST(0 AS DATE)

With exception: Explicit conversion from data type int to date is not allowed.

As should be obvious, something isn't quite fleshed out with the new types.

2) This limitation is documented here: http://msdn.microsoft.com/en-gb/library/ms189491.aspx

The DATEFORMAT ydm is not supported for date, datetime2 and datetimeoffset data types.

Why is this so? Inside the database engine DATE, DATETIME2 and DATETIMEOFFSET take different code paths than DATETIME and SMALLDATETIME (you can validate this with a profiler). Because the new date formats arrived in the SQL Server code base long after DATETIME/SMALLDATETIME - different programmers worked on it and hence, some functionality is not 100% aligned (yet). Another example: In early versions of SQL Server 2008R2, bulk loading a DATE was much slower than bulk loading a SMALLDATETIME, even though one is smaller than the other. This was fixed before release. It's the kind of thing that happens in a big organisation with lots of coders.

3) This means that it is not possible for you to know if the date format is valid until you actually try it. In your case, it means that even if you do an "show query plan" without execution, you will not know that there is an error in the format until you actually execute the query.

  • one small doubt . canonical format means default format ? – IT researcher Dec 10 '14 at 13:23
  • Define what you mean by default format please? Maybe this answers it: The internal storage of the date formats are just offsets (in days, seconds, millisec etc) from some known point in time. Very similar to unix handling of time. – Thomas Kejser Dec 10 '14 at 13:26
  • I meant to say the date will be stored in yyyy-mm-dd format (default)itself.Default format set based on language i guess – IT researcher Dec 10 '14 at 13:36
  • I added some more information to the answer about the internal format. Hope that helps – Thomas Kejser Dec 10 '14 at 16:15
  • 2
    @ITresearcher NO. Dates are NOT stored in any friendly string format like you are suggesting. This is merely how they might be represented to you by client applications like SSMS or through providers like OLEDB/ODBC. – Aaron Bertrand Dec 10 '14 at 23:59
2

Although not a direct answer to the question, I'm not entirely convinced that the examples that were shown in the blog post you linked to were really a good example of SET DATEFORMAT.

One of the key reasons you should use DATEFORMAT, is when a date can be interpreted in multiple ways.

If you work for a global business where the date formats vary between countries, it is vital that you use DATEFORMAT to specifically set the format you're expecting to receive.

Say I lived in the US and imported a file from colleagues in the UK. In the example below, the date imported will be different depending on your LANGUAGE settings.

CREATE TABLE #tempTable (DateFormatSample DATE, ID INT IDENTITY(1,1));

INSERT INTO #tempTable
VALUES ('08/07/2007');

INSERT INTO #tempTable
VALUES ('07/08/2007');

Based on my US language setting, this inserts:

DateFormatSample    ID
2007-08-07           1
2007-07-08           2

However if I set the DATEFORMAT to UK format.... :

CREATE TABLE #tempTable (DateFormatSample DATE, ID INT IDENTITY(1,1));

SET DATEFORMAT DMY;

INSERT INTO #tempTable
VALUES ('08/07/2007');

INSERT INTO #tempTable
VALUES ('07/08/2007');

I get a completely different day and month:

DateFormatSample    ID
2007-07-08           1
2007-08-07           2

The query will not error, because the date is valid, however you'll end up with unexpected results. You could import a full 12 days of data in the wrong format every month, throwing your system into chaos.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.