SQL Server – cast string into date vs datetime

I found a tricky question on http://www.sqlservercentral.com/Questions about casting strings into date vs datetime, and I want to share this with you, in case you missed it. (by the way, excellent site if you want to measure your knowledge and learn something new, thanks SQL server central)

Try the following code:

SET DATEFORMAT YDM;
GO

CREATE VIEW vDate(dt1, dt2)
AS
SELECT CAST(‘2012-04-10’ AS DATETIME),CAST(‘2012-04-10’ AS DATE
)
GO

SET DATEFORMAT YDM;
GO
SELECT * FROM vDate WHERE dt1=dt2;

SET DATEFORMAT YMD;
GO
SELECT * FROM vDate WHERE dt1=dt2;

DROP VIEW vDate; 

It turns out that the two selects from the veiw doesn’t return the same result! 🙂 The string casted to datetime is depending on the regional setting but date is not. The date datatype is language neutral and will always be casted as yyyymmdd.

If you want to learn more about how date and datetime are handled by the sql server engine you can read more on Tibor Karaszi’s blog http://www.karaszi.com/SQLServer/info_datetime.asp or contact any of our SQL server consultants.