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.
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.