We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

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

Sigh_Man
Sigh_Man asked
on
Medium Priority
358 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...
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
this will work better:

SELECT convert(smalldatetime, Convert(char(20), DATEADD(dd,1,getdate()), 101) ,101)
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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?

Commented:
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())))))

Author

Commented:
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.

Author

Commented:
Can anyone tell me how to set the DEFAULT DATEFORMAT to mdy (say) - PERMANENTLY, not just for the session???
Commented:
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...
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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)

Author

Commented:
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!)
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>for example in the connection string?
you might specify it in the properties of the login, by setting the adequate language.

Author

Commented:
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?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>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).

Author

Commented:
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.
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
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

Author

Commented:
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???
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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...

Author

Commented:
Fantastic - this problem had been driving me crazy.  Thanks to all who commented.  I hope the points-split is OK.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.