date time format conversion

Question

Post reply

date time format conversion

ekant_alone

SSCrazy

Points: 2423
More actions
September 21, 2011 at 10:20 am

#244207

I know this is very silly thing but i am kind of lost. sometimes 2 + 2 = 5 happens
script is like this
create table test
(
col1 datetime
)
insert into test values (convert(datetime,'22/5/2011 10:09:40 PM',101))
error:
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Ninja's_RGR'us

SSC Guru

Points: 294069

September 21, 2011 at 10:26 am

#1384649

SET DATEFORMAT DMY

Mike John SSCertifiable Points: 7216 More actions · Answer 2

The "Style" 101 you have used in the convert is US dateformat so your conversion is working as MM/DD/YYYY

You could use style 103 if you want the dates input as dd/mm/yyyy or (preferably in my view) aways supply dates as an unambiguous string YYYYMMDD and remove the convert and style altogether.

Mike John

HowardW One Orange Chip Points: 29109 More actions · Answer 3

The key point here is you're misinterpreting the optional Style parameter of convert. It is not for converting a character value input into a datetime using a specific style, it's for outputting a specific style from a date/datetime format. It's entirely ignored when the input is not a date/datetime.

If you're capturing a datetime from an external application, you should make sure it passes the value in a datetime format, rather than as a character value to avoid these kinds of conversion issues.

HowardW One Orange Chip Points: 29109 More actions · Answer 4

Mike John (9/21/2011)
The "Style" 101 you have used in the convert is US dateformat so your conversion is working as MM/DD/YYYY
You could use style 103 if you want the dates input as dd/mm/yyyy or (preferably in my view) aways supply dates as an unambiguous string YYYYMMDD and remove the convert and style altogether.
Mike John

The style parameter has nothing to do with converting a varchar/char to a datetime.

drew.allen SSC Guru Points: 76985 More actions · Answer 5

HowardW (9/21/2011)

The style parameter has nothing to do with converting a varchar/char to a datetime.

You might want to recheck your facts. BOL says differently:

http://msdn.microsoft.com/en-us/library/ms187928.aspx
Input when you convert to datetime; output when you convert to character data.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

HowardW One Orange Chip Points: 29109 More actions · Answer 6

drew.allen (9/21/2011)

HowardW (9/21/2011)
The style parameter has nothing to do with converting a varchar/char to a datetime.
You might want to recheck your facts. BOL says differently:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Input when you convert to datetime; output when you convert to character data.
Drew

Hmm, well you learn something new every day. What threw me is a quote in the same BOL article which says:

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

and

Date and Time Styles
When expression is a date or time data type, style can be one of the values shown in the following table. Other values are processed as 0. SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm.

Am I missing something, or is expression not a varchar/char data type, therefore should be processed as zero. Maybe I'm misreading, or is this a mistake in BOL? (you'd think not for the amount this article is used)

Edit: And apologies to Mike for saying you were incorrect

Mike John SSCertifiable Points: 7216 More actions · Answer 7

No problem Howard.

"The man who makes no mistakes does not usually make anything." ~ Edward Phelps

You had me double checking for a few minutes though. Re-reading BOL it is a bit open to interpretation and I spent a long time originally thinking like you that it was only relevant converting from datetime.

Cheers

Mike John

HowardW One Orange Chip Points: 29109 More actions · Answer 8

Thanks for being so gracious!

I think BOL's just plain wrong. The SQL Server 2005 article says:

Is the style of the date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or to convert character data of known date or time formats to datetime or smalldatetime data

So either they just made a mistake removing this text, or they plan to deprecate it/make it undocumented and have only partially done so...

Mike John SSCertifiable Points: 7216 More actions · Answer 9

As you say there are some interesting differences between versions of BOL. I hope it is just slightly confused wording though, rather than the removing it!

Mike

drew.allen SSC Guru Points: 76985 More actions · Answer 10

HowardW (9/21/2011)
Maybe I'm misreading, or is this a mistake in BOL? (you'd think not for the amount this article is used)

Manuals are not technical specs nor are they gospel. They should DESCRIBE the actual behavior, not PRESCRIBE the correct behavior.

I always TEST before posting. Maybe you should consider the same.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

HowardW One Orange Chip Points: 29109 More actions · Answer 11

drew.allen (9/22/2011)
I always TEST before posting. Maybe you should consider the same.
Drew

I'm not sure how that comment's helpful.

In this case, I thought I knew the functionality well enough, did a quick check on BOL before posting (which seemed to corroborate my understanding) and then posted. I know I was incorrect and quickly apologised for the fact.

Also, I find these statements to be contradictory:

Manuals are not technical specs nor are they gospel. They should DESCRIBE the actual behavior, not PRESCRIBE the correct behavior.

and:

drew.allen (9/21/2011)
You might want to recheck your facts. BOL says differently:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Input when you convert to datetime; output when you convert to character data.

I also fundamentally disagree that the purpose of documentation isn't to be accurate. It doesn't need to describe everything, but it shouldn't contradict itself or make incorrect statements. Everyone uses BOL to check the usage of a function, should you feed parameters that the documentation says aren't used by trial and error in order to learn everything?

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 12

The documentation issues aside, I think Drew is saying it's easy to run a test and see if supplying a style as any affect on conversion from string to date:SET DATEFORMAT MDY -- US

DECLARE @Foo VARCHAR(100) = '2/12/2010'

SELECT CONVERT(DATETIME, @Foo, 110), CONVERT(DATETIME, @Foo, 103)

HowardW One Orange Chip Points: 29109 More actions · Answer 13

Absolutely. Which is exactly what I did once Drew pointed it out and apologised for my mistake.

drew.allen SSC Guru Points: 76985 More actions · Answer 14

HowardW (9/22/2011)I also fundamentally disagree that the purpose of documentation isn't to be accurate.

No one said that it wasn't the goal. What I was trying to say is that it is unrealistic to expect it to be PERFECT. This is one case where what BOL says differs from actual results.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA