?
Solved

SELECT statement with dates works on one machine but not on another

Posted on 2007-07-20
17
Medium Priority
?
323 Views
Last Modified: 2008-02-20
I have 4 machines all running their own SQL databases.  The following statement:
SELECT CAST(Convert(char, DATEADD(dd,1,getdate()), 101) as smalldatetime)
gives the following error on one machine:
"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value".
This error only began recently, so I think I must have inadvertently changed the default DATE STYLE/FORMAT or something on this one problem machine.  I think this is something to do with the default DATESTYLE on this one particular machine because if I change the format in this statement to 103, ie SELECT CAST(Convert(char, DATEADD(dd,1,getdate()), 103) as smalldatetime)    ...then it returns 2007-07-21 00:00:00.000 (the same result as the other 3 machines).  Problem is, it is happening on my main development machine so I have to build solutions and test them on one of the other machines - hardly practical!!  :D  I need all machines to have the same default date style.
I am aware of the many functions such as DATEPART for dealing with different date formats, but there must be something I can change on this problem machine to make it behave the same as the others.
500 points cos this is really beginning to hold me up!
Thanks in advance...
0
Comment
Question by:Sigh_Man
  • 8
  • 5
  • 2
  • +2
17 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19529987
this will work better:

SELECT convert(smalldatetime, Convert(char(20), DATEADD(dd,1,getdate()), 101) ,101)
0
 
LVL 12

Assisted Solution

by:patrikt
patrikt earned 200 total points
ID: 19530003
Yes, agree with angelII.

Explanation why your original will not works is default DATEFORMAT. If using implicit cast as you do it depend on DATEFORMAT and LANGUAGE seting. One of your server has MDY and second has DMY DATEFORMAT.
0
 
LVL 1

Author Comment

by:Sigh_Man
ID: 19530335
thanks for your responses.  I know there are workarounds to make this particular select statement work, but what I am trying to achieve is to have all machines consistent with each other.  To that end, Patrikt, how do I change the DATEFORMAT and LANGUAGE setting?
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 11

Expert Comment

by:deroby
ID: 19530366
Why oh why do people always want to convert() datetimes to string and back to datetimes ????

This ALWAYS works, and is probably faster too :

SELECT DateAdd(dd, 1, DateAdd(hour,          - DatePart(hour, GetDate()),
                      DateAdd(minute,        - DatePart(minute, GetDate()),
                      DateAdd(second,        - DatePart(second, GetDate()),
                      DateAdd(millisecond, 1 - DatePart(millisecond, GetDate()), GetDate())))))
0
 
LVL 1

Author Comment

by:Sigh_Man
ID: 19530573
Deroby: The reason I chose the particular convert technique is because there seemed to be an abundance of literature advocating this particular approach.  I have no particular affinity to my choice of SELECT statement; I did mention in the question that I was aware of DATEPART and other ways around this issue, but the main purpose of this question was to find a way to make my machine 1 behave in exactly the same way as machines 2, 3 and 4.  In that regard, I need to change the underlying DATEFORMAT.
0
 
LVL 1

Author Comment

by:Sigh_Man
ID: 19531079
Can anyone tell me how to set the DEFAULT DATEFORMAT to mdy (say) - PERMANENTLY, not just for the session???
0
 
LVL 11

Assisted Solution

by:deroby
deroby earned 200 total points
ID: 19532344
Convert() : yeah I know, somehow it's a bad habbit that seems to have spread to far now to be fixed anymore =(

Anyaway, dateformat is linked language, so you need to look at the default language of the server.
Go to server properties and check what's being used there.
(advanced page in sql2k5, sql mgmt studio, server-settings tab in SQL2K Enterprise Mgr)
Looking in the help you'll probably find the link between dateformat and language, or you can run sp_helplanguage on the server to get a list.

Mind that these are the default settings, so if a connection decides to use another setting, you're still screwed...
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 19535551
Don't use 101 format, as it is installation dependent; use 112 (yyyymmdd) instead.
SELECT CAST(Convert(char(8), DATEADD(dd,1,getdate()), 112) as smalldatetime)
Personally I prefer that for low volume dates, esp. GETDATE() which only be converted *once*, because I think it's easier to follow.

If you have a lot of dates or just prefer a "date math" solution, use this:
SELECT DATEADD(DAY, FLOOR(CAST(GETDATE() AS FLOAT)), 0)
0
 
LVL 1

Author Comment

by:Sigh_Man
ID: 19538214
Thanks for all the responses.  With this default language/dateformat thing: Is it possible to set this somewhere once only - for example in the connection string?  Setting this in the connection string would be far easier than my prefixing each and every one of my select statements with 'SET DATEFORMAT mdy' or 'SET LANGUAGE US_ENGLISH (or whatever the syntax)'!  (Particularly since it is only this one machine that has started misbehaving!)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19538246
>for example in the connection string?
you might specify it in the properties of the login, by setting the adequate language.
0
 
LVL 1

Author Comment

by:Sigh_Man
ID: 19542226
angelIII, THAT'S IT -- thank you!!!  My "problem machine" was, for some unknown reason, set to British English, whilst the other machines were set to US_English.
I would like to add something to my script file to ensure that a login uses the correct default language. What would be the SQL syntax to set the sa login default language to US_English?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19542232
>What would be the SQL syntax to set the sa login default language to US_English?
while you can do it with sql, you can do it using enterprise manager, in the security folder -> logins -> sa login (edit in the right click).
0
 
LVL 1

Author Comment

by:Sigh_Man
ID: 19542240
Yeah, I know I can use EM, but I want this to execute automatically - ie behind the scenes.  I have an SQL script file which my app executes every time it updates itself, and I would like to add this language-check to that script file - just incase any machines are not set to US_English.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 19542248
sql 2005:
ALTER LOGIN sa WITH DEFAULT_LANGUAGE = US_English
http://msdn2.microsoft.com/en-us/library/ms189828.aspx

sql 2000 or lower:
EXEC sp_defaultlanguage 'sa', 'US_English'
http://doc.ddart.net/mssql/sql70/sp_da-di_7.htm

0
 
LVL 1

Author Comment

by:Sigh_Man
ID: 19542266
Excellent.  Of course, just to be awkward, I have some users on SQL2000 (MSDE) others on SQLexpress.  I don't suppose there is a statement that would work in both?  Or, if not, how could I say:
IF db_Instance is SQL2000 then
   EXEC sp_defaultlanguage 'sa', 'US_English
ELSE
   ALTER LOGIN sa WITH DEFAULT_LANGUAGE = US_English
END

OR, should I somehow do a try...catch and run both???
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19542271
I don't have sql 2005 running at home, so I have to assume according to the documentation that the procedure way works also in sql 2005...
0
 
LVL 1

Author Comment

by:Sigh_Man
ID: 19542277
Fantastic - this problem had been driving me crazy.  Thanks to all who commented.  I hope the points-split is OK.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question