May 24, 2012 at 4:54 am
Hopefully a quick post.
I am running an ASP.net 4.0 website with backend database SQL Server 2008. (hosted on GoDaddy)
I have recently copied my production website onto my pc and have also installed SQL Server 2008 Express so I can develop it and the production database has been copied onto my PC.
When I run the website from my PC I get:
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
This works fine on my actual website so it has to be something with the date format on the SQL Server Express running on my PC.
When I run selet getdate() on the 2 sql servers I receive:
GoDaddy SQL Server:
5/24/2012 3:52:43 AM
My local SQL Server:
2012-05-24 06:52:33.957
What do I need to change on my local SQL Server to get the date to the format seen on the GoDaddy server?
Thanks for all replies.
May 24, 2012 at 5:01 am
You should always use ISO format (YYYY-MM-DD) when converting string values into datetime (or date). This will work regardless of server locale.
May 24, 2012 at 5:07 am
+1 eugene, but also - check the default language of your login on the local machine - if you are in BRITISH rather than ENGLISH (which i guess godaddy may have set up for you) then you get issues when dealing with any date where the day of month is > 13
MVDBA
May 24, 2012 at 6:30 am
Thanks guys.
I have verified that both servers are using ENGLISH as the default language.
May 24, 2012 at 6:55 am
It's to do with server locale.
May 24, 2012 at 7:11 am
Do you know how and where to change it?
May 24, 2012 at 7:21 am
It's pointless. You shouldn't rely on this.
You need to change your code (and coding practice)
Whenever you are using string datatype for date values, ensure that unambiguous format is used (ISO or letter-month - YYYYMMDD or 10 May 2012).
In all other cases you will always have a risk that one day 1/5/2012 will be taken as 1 May and another day it will be 5 Jan.
You can use SET DATFORMAT to tell SQLServer how to treat string-dates in a session, but again, the best way is to use ISO!
May 24, 2012 at 8:41 am
Eugene Elutin (5/24/2012)
It's pointless. You shouldn't rely on this.You need to change your code (and coding practice)
Whenever you are using string datatype for date values, ensure that unambiguous format is used (ISO or letter-month - YYYYMMDD or 10 May 2012).
In all other cases you will always have a risk that one day 1/5/2012 will be taken as 1 May and another day it will be 5 Jan.
You can use SET DATFORMAT to tell SQLServer how to treat string-dates in a session, but again, the best way is to use ISO!
+
also set language options (in some circumstances) - especially where people do convert(varchar(40),getdate()) and end up looking for the word "apr" (april) - unfortunatly in some languages (all of them that i speak are different) months are called diferent things french for april is avril, polish is kwiecien etc
if you want further info on this Itzik ben gan's books on querying SQL server have very clear info on what the only specified date/time formats are that will be locale/language neutral
MVDBA
May 24, 2012 at 8:51 am
The universally accepted date format for SQL Server is "YYYYMMDD" (as in 20120524), not YYYY-MM-DD, which different installations will treat differently (and some will error out).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 24, 2012 at 9:06 am
ScottPletcher (5/24/2012)
The universally accepted date format for SQL Server is "YYYYMMDD" (as in 20120524), not YYYY-MM-DD, which different installations will treat differently (and some will error out).
Although I'm usually using YYYYMMDD, I have never-ever seen YYYY-MM-DD to fail - they are both ISO.
May 24, 2012 at 10:51 am
I have never-ever seen YYYY-MM-DD to fail
Please try this 🙂 :
SET DATEFORMAT dmy
SELECT CAST('20120524' AS datetime)
SELECT CAST('2012-05-24' AS datetime)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply