Datetime Format - 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

SQL Server 2005 Forums

SQL Server Administration (2005)

Datetime Format

Author

Topic

afzaal820
Starting Member

19 Posts

Posted - 2011-09-20 : 22:15:13

Hi

Platform:
Window Server 2008 R2
SQL Server 2008

When I run this statement even on the query window. It say that date is out of range.

Can anybody tell what will be the possible reasons for that?

Update Table set Field= '2011-01-20' where ID = 1

Field - Datetime.

I know it is something to do with system culture but If I am not wrong, ISO format is the default format for the SQL Server and it should accept.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 22:32:09

what about this?

Update Table set Field= '20110120' where ID = 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-20 : 22:35:44

Hmmm, I wonder if your dateformat is set different than ymd.

Maybe try this?
[CODE]
declare @table table (id int, field datetime null)
insert into @table (id)
values (1)

SET DATEFORMAT ymd;

Update @Table set Field= '2011-01-20' where ID = 1

select *
from @table
[/CODE]

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-20 : 22:47:23

The syntax Visakh showed (ccyymmdd) works for all date formats.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-20 : 22:54:05

Ah, thanks Russell. Good to know! I started my post before he posted, so didn't see his solution :)

afzaal820
Starting Member

19 Posts

Posted - 2011-09-21 : 02:32:29

Hi All

@Visakh - this is just a small example but in actuall aur date are stored in XML in ISO format and we index it in a custom index table for searching purpose. So when we extract from xml and try to fill index table it throws error parsing(out of range). We cann't change all our xml date into new format because it has a big impact.

@flamblaster - we used similar solution last night and it worked, but here my question is why we need to set the format to ymd as my believe is that ISO format is default SQL server format and it should work without setting the specific format.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-21 : 02:38:59

Not sure, was just trying to figure out what might be going on. I don't know enough about the inner workings of SQL Server to know if it's even possible to change the default date format or not. Since you were passing a valid date into a datetime field and it was saying the date was out of range, the only thing that I could think of was that maybe it was interpreting the "20" portion of '2011-01-20' as the month part of the date. This would only really make sense (at least to me) if the default date format was something other than ymd.

afzaal820
Starting Member

19 Posts

Posted - 2011-09-21 : 03:09:19

thanks for your reply,

do you have any idea how to set default SQL server dateformat to ymd rather then using

SET DATEFORMAT ymd;

because my only worry is if we missed it at any place and we are not able to catch it during the staging(UAT) then it can be a problem in production.

Also I noted that the datetime column I see on the server showing the date time in the ISO format. it is really wierd.

afzaal820
Starting Member

19 Posts

Posted - 2011-09-21 : 04:02:47

SET LANGUAGE british
SELECT CAST('2003-02-28' AS datetime)

this is interesting :).

Why I don't know can a date be dependent on Language..

afzaal820
Starting Member

19 Posts

Posted - 2011-09-21 : 04:23:20

Another interesting thing I have found that if SQL server is running on Network Service Account or Local Service or Local System. It has different ways of picking the default language.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 05:46:10

quote:
Originally posted by afzaal820

Another interesting thing I have found that if SQL server is running on Network Service Account or Local Service or Local System. It has different ways of picking the default language.

That might because default language associated to those logins are different

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-09-21 : 18:19:29

If you have "string" dates use "YYYYMMDD" or "YYYYMMDD hh:mm:ss.sss" or "YYYY-MM-DDThh:mm:ss.sss"

Be very careful about punctuation - only the above are always treated unambiguously.

Anything else SQL will do its best to interpret. You can force the parser to a particular style of dates by using CONVERT(datetime, MyDateValue, 999) [where "999" is a date style indicator, see DOCs for actual values]. Or you can use

SET DATEFORMAT ymd

to force the parser to a particular style. But its a slippery slope.

It is regrettable, IMHO, that the DEVs who originally built SQL Server allowed it to attempt to interpret any date format. As a consequence dates like "01-02-03" might be interpreted as "01-Feb-2003", "Jan 02, 2003" or even "2001-Feb-03"

Without other explicit instructions SQL Server will (I think!) use the Locale setting on the server. If you move to a different server that could change of course ... as it will if the server's locale configuration is changed.

If a user connects and changes Language, or Country, that will change the default date parsing interpretation ...

Long-story-short: always use "YYYYMMDD", "YYYYMMDD hh:mm:ss.sss" or "YYYY-MM-DDThh:mm:ss.sss" formats only

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources