Search code examples
sql-serversql-server-2008datetimecastingnvarchar

CAST nvarchar to datetime regardless of login date format?

I've got 2 servers with two different login date formats (English & British English).

I need to be able to CAST an nvarchar value to datetime regardless of the date format.

I have the following query:

select cast('2011-13-07' as datetime)

This works on 1 server, but not the other.

Is there another way I can represent 13/7/2011 as a datetime object for both servers?

For consistency, the query needs to be identical on both servers.

Solution

  • Use the ISO-8601 standard format YYYYMMDD:

    SELECT CAST('20110713' as DATETIME)

    Don't use any dashes! That format will work on any SQL Server instance, regardless of language, regional, locale settings - it just works!

    The other format in ISO-8601 is YYYY-MM-DDTHH:MM:SS - this one includes dashes, but also a time portion.