SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Datetime Format
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

afzaal820
Starting Member

Singapore
19 Posts

Posted - 09/20/2011 :  22:15:13  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 09/20/2011 :  22:32:09  Show Profile  Reply with Quote
what about this?

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

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

Go to Top of Page

flamblaster
Constraint Violating Yak Guru

380 Posts

Posted - 09/20/2011 :  22:35:44  Show Profile  Reply with Quote
Hmmm, I wonder if your dateformat is set different than ymd.

Maybe try this?

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


Edited by - flamblaster on 09/20/2011 22:36:04
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 09/20/2011 :  22:47:23  Show Profile  Visit russell's Homepage  Reply with Quote
The syntax Visakh showed (ccyymmdd) works for all date formats.
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

380 Posts

Posted - 09/20/2011 :  22:54:05  Show Profile  Reply with Quote
Ah, thanks Russell. Good to know! I started my post before he posted, so didn't see his solution :)
Go to Top of Page

afzaal820
Starting Member

Singapore
19 Posts

Posted - 09/21/2011 :  02:32:29  Show Profile  Reply with Quote
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.




Go to Top of Page

flamblaster
Constraint Violating Yak Guru

380 Posts

Posted - 09/21/2011 :  02:38:59  Show Profile  Reply with Quote
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.
Go to Top of Page

afzaal820
Starting Member

Singapore
19 Posts

Posted - 09/21/2011 :  03:09:19  Show Profile  Reply with Quote
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.



Go to Top of Page

afzaal820
Starting Member

Singapore
19 Posts

Posted - 09/21/2011 :  04:02:47  Show Profile  Reply with Quote
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..
Go to Top of Page

afzaal820
Starting Member

Singapore
19 Posts

Posted - 09/21/2011 :  04:23:20  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/21/2011 :  05:46:10  Show Profile  Reply with Quote
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/

Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/21/2011 :  18:19:29  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000