Date time issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date time issue

Author

Topic

deepakdba
Starting Member

2 Posts

Posted - 2015-04-27 : 01:11:13

hi

am using a variable with varchar (25)
am getting value for that as "25/04/2015"

while executing the sp (inserting the value from variable to a single column table which have a column having same data type as above)

am getting the error

Msg 242, Level 16, State 3, Procedure XXXXXXXXXXXXXX, Line 154
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

but the same sp executing sucessfully for the input "04/25/2015" and the desired result is fetching, can anyone clarify y this is happening like this,

output for getdate()
select getdate()
"2015-04-27 10:40:22.560"

Thanks in Advance!!!!
With Regards
deepak Subramoniam Be,Oca

Maithil
Starting Member

29 Posts

Posted - 2015-04-27 : 01:30:21

Hi

kindly USE FORMAT for Desired DATE FORMAT

for your Scenario

Use

select FORMAT(Your_date_Column,'MM/dd/yyyy')

Hope this will sort out your Issue.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-04-27 : 01:45:15

You should use a more universal and cross platform method. For DATE only portion use YYYYMMDD.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

deepakdba
Starting Member

2 Posts

Posted - 2015-04-27 : 02:04:03

Thanks All,

I got the point, but i want to know more about this,

can anyone explain why this was happening like this,

where i can to check the root cause to update my knowledge

Thanks in Advance!!!
With Regards
Deepak Subramoniam B.e,OCA

Kristen
Test

22859 Posts

Posted - 2015-04-27 : 07:00:01

quote:
Originally posted by deepakdba

can anyone explain why this was happening like this

If you use

"25/04/2015"

as your Date SQL will attempt to parse it. HOW it does that DEPENDS ... on lots of things. The Locale of the Server, the Language setting for the currently logged on user, any DATEFORMAT option currently active ... lots of things.

So your

"25/04/2015"

might be being parsed as D/M/Y (as you hope), or as "m/d/y" (probably more common as the default is usually USA format), or even as "y/m/d" ... in fact it could be any sequence.

A much MORE serious issue is if you attempted to use the date "1/2/03". In your case, of d/m/y, that would be 01-Feb-2003, but as your SQL is NOT parsing dates in that format it might well take it to be "02-Jan-2003". In THAT case there would be NO error message, but clearly the date would not be what you intended.

Even if you, now, provide the date as "04/25/2015" there is NO guarantee that the date will be correctly parsed in the future. THe Locale of your server may change, or the logged on user might set a different Language (French for example ...) which changes the date parsing rules.

So ... as JackV said ... only EVER use a date in 'YYYYMMDD' format. Do NOT include ANY punctuation. "YYYYMMD"" is NOT the same as "YYYY/MM/DD". In the first case, of 6 digits, SQL will ALWAYS use YYYYMMDD parsing logic. In the second case it will see the "/" and attempt to parse it according to Current Locale / Language etc etc etc.

AuroraS
Starting Member

3 Posts

Posted - 2015-04-27 : 07:33:21

The problem you mentioned seems to be a problem with the time zone settings. Specifying the date as mm/dd/yyyy it works while specifying an other format as dd/mm/yyyy it results in an error.That is why you have to convert your input looking at FORMAT or CONVERT functions.

Kristen
Test

22859 Posts

Posted - 2015-04-27 : 07:44:47

quote:
Originally posted by AuroraS

The problem you mentioned seems to be a problem with the time zone settings

This is not an issue of Time Zones, the error is caused by the way that SQL chooses to Parse the (ambiguous) date.

quote:
specifying the date as mm/dd/yyyy it works while specifying an other format as dd/mm/yyyy it results in an error.

The problem with "it works" is that it is not guaranteed to carry on working! To solve that you have to use an unambiguous date.

Note that the O/P is INSERTING data into a table, not displaying it.

If the user-provided data is in the format "dd/mm/yyyy" then it does, indeed, need to be converted so that SQL will UNAMBIGUOUSLY parse the data. That can be done using the CONVERT function, string manipulation, or SET DATEFORMAT, etc. - the best route choosen is likely to depend on what version of SDQL the O/P is using

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-05-07 : 05:39:40

quote:
Originally posted by deepakdba

Thanks All,

I got the point, but i want to know more about this,

can anyone explain why this was happening like this,

where i can to check the root cause to update my knowledge

Thanks in Advance!!!
With Regards
Deepak Subramoniam B.e,OCA

You may need to read this post http://beyondrelational.com/modules/2/blogs/70/posts/10898/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2015-05-07 : 05:48:03

Interesting article Madhi.

Have you explored how SQL handles DATE datatype?

It seems that


SELECT CONVERT(date, '2015-01-02')

is unambiguous ... whereas converting to DATETIME is NOT.

Converting to date the 'yyyy-mm-dd' format ignores any DATEFORMAT and LANGUAGE setting. I haven't seen anything in BoL that actually confirms this, but it seems to me that someone with both DATE and DATETIME datatypes in their APP might get caught out by implicit conversions being unambiguous to DATE but dependent on Locale, Language, DATEFORMAT, etc settings when implicitly converting to DATETIME.

Hence my advice remains to stick to "yyyymmdd" ... but I can see even more people coming unstuck with this in future ...

I haven't checked how the other new data type like DATETIME2 etc behave ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-05-07 : 06:30:58

Yes from version 2008 onwards hyphenated YMD format is unambiguous too only for DATE datatype. But I prefer YYYYMMDD as it works for all DATE related datatypes

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2015-05-07 : 14:31:52

quote:
Originally posted by madhivanan

I prefer YYYYMMDD as it works for all DATE related datatypes

100% agree with that!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-05-08 : 08:42:20

quote:
Originally posted by Kristen

quote:
Originally posted by madhivanan

I prefer YYYYMMDD as it works for all DATE related datatypes

100% agree with that!!

I think as YYYY-MM-DD is more readable MS might have made it unambiguous for DATE datatype

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2015-05-08 : 10:48:52

hard to know if CONSISTENT would be better ... have to give up on LEGACY at some point though. But there are lots of goofy things that I would have expected to have been thought-through before they went into production ...

VARCHAR(MAX)
DATETIME2
BIGINT

There's probably a Long-something in there somewhere ...

A compatibility mode setting, for the new SQL version, where you said "Only access ambiguous dates in "yyyy-dd-mm" format would allow any developer who was using a specific date format style (but ONLY ONE per database) to ensure that implicit conversions were UNambiguous, and retained backwards compatibility with their APP, and then at that point MS could discontinue all other AMbiguous date formats and do us all a favour ...

... CONVERT would still be available for the rest, or a CONFIG Compatibility Setting of "same implicit ambiguous conversion as before"

... it would reduce my SQL Team answers by the 90% that consist purely of "Be careful with your implicit DATETIME conversion" ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources