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.

Answers


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.


Need Your Help

Page_load filling data after loading UserControl ASP.net

asp.net user-controls ascx pageload

I have an aspx Page that contain a userControl that contains textboxes.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.