SET DATEFORMAT problems

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?

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.

StackExchange Database Administrators Q#84790, answer score: 6