CODEWITHSUNDEEP
sql-server-2008t-sql
japesu
japesu

Reputation: 134

SQL Server (T-SQL) datetime conversion

A silly question maybe but I wanted clarification. I've created a script that has a date parameter like so:

DECLARE @dateparam as datetime
SET @dateparam = '01-01-2013 00:00:00'

This looks like it is working when I test it even if the date string is not in "correct" format yyyy-MM-dd hh:mm:ss. I changed my computer regional settings to English and the script still did what it was supposed to do.

Is this because of SQL Server 2008 R2 that I have in my computer that it knows how to convert the date or can I ran into trouble with using a dateformat like I have used?

Upvotes: 1

Views: 287

Answers (2)

tjibbe chris
tjibbe chris

Reputation: 160

You're making an implicit conversion from something that looks like a date, but inf fact is a string ( '01-01-2013 00:00:00'). Rather than trusting on SQL Server to make the correct guess in what format the string is in, you should make the conversion explicit by specifying the format.

This can be done by using CONVERT (not CAST) and specify a 'style'. The different styles are listed here: http://msdn.microsoft.com/en-us/library/ms187928.aspx.

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

Converting 01-01-2013 won't expose issues such as which 01 is the month, and which is the day.

It's not a safe format.

The safe formats (for converting to datetime, rather than to datetime2) are:

YYYYMMDD                               20121201
YYYY-MM-DD'T'hh:mm:ss                  2012-12-01T10:43:29
YYYY-MM-DD'T'hh:mm:ss.mil              2012-12-01T10:43:29.337

Stick to those and only those. (The examples all represent the 1st December 2012)

Or, better yet, don't treat dates as strings at all, if you can avoid it. If you're, for example, calling SQL Server from .NET code, keep that dates as DateTimes in your code, and let ADO.NET and SQL Server deal with any required translations to make them become datetimes - without translating them to and from strings.

Upvotes: 3

Related Questions