August 11, 2009 at 6:19 am
Hi all,
Simple question to see if this is the correct behaviour and if not what would resolve it.
I have 2 sql 2005 servers that this occurs on, and all of my 2000 servers so it is the same everywhere I test it.
Everything is set to British English or English (UK) on the operating system and the sql server AND the logins. But if you run the following code you get the old error -
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
So I get the problem with the following dates -
select convert(datetime,'31/12/2009',120) --FAILS WITH THE ABOVE
select convert(datetime,'31/12/2009') --WORKS
select convert(datetime,'2009-06-30',120) --WORKS
select convert(datetime,'2009-06-30') --FAILS WITH THE ABOVE
Futhermore
SELECT isdate('31/12/2009') -- Returns True
SELECT isdate('2009-06-30') -- Returns False
Is there a fundimental issue on all of my servers? I need to handle this as we have dates coming into our system from various formats and we need to convert and then check them all.
Thanks for your help
:hehe:
August 11, 2009 at 6:45 am
Check your default language in the server properties, advanced window.
SET LANGUAGE implicitly sets the setting of SET DATEFORMAT.
Regards
Gianluca
-- Gianluca Sartori
August 11, 2009 at 6:54 am
Yep British English
August 11, 2009 at 6:58 am
As long as you know what format the dates you are receiving are in, then format them into YYYYMMDD format.
That way, you shouldn't have a problem, regardless of the language setting on the server.
August 11, 2009 at 7:04 am
That would hacve been fine, except as I mention the dates are of varying formats. If we just convert to datetime with no format then that should put it to sql preferred yyyy/mm/dd hh:mm but as you can see that fails if the date is already in yyyy/mm/dd format for some reason.
Does EVERYONE get these issues?
August 11, 2009 at 7:08 am
As above post, I'm in the UK and setting the default login language to "British English" will render the format "YYYY-MM-DD" useless unless actually specified with 120 in a convert statement.
We just use "English" on all our servers and manually format dates in reports etc. British English has caused some bizarre results!
Under British English, YYYYMMDD will work implicitly and so will CCYY-MM-DDTHH:MM:SS.sss, but it expects YYYY-DD-MM for some reason.
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
August 11, 2009 at 7:09 am
This is what I get in my US-English instance:
select convert(datetime,'31/12/2009',120) --FAILS
select convert(datetime,'31/12/2009') --FAILS
select convert(datetime,'2009-06-30',120) --WORKS
select convert(datetime,'2009-06-30') --WORKS
SELECT isdate('31/12/2009') -- Returns False
SELECT isdate('2009-06-30') -- Returns True
-- Gianluca Sartori
August 11, 2009 at 7:10 am
Rob Goddard (8/11/2009)
As above post, I'm in the UK and setting the default login language to "British English" will render the format "YYYY-MM-DD" useless unless actually specified with 120 in a convert statement.We just use "English" on all our servers and manually format dates in reports etc. British English has caused some bizarre results!
Under British English, YYYYMMDD will work implicitly and so will CCYY-MM-DDTHH:MM:SS.sss, but it expects YYYY-DD-MM for some reason.
That's why I'm in Italy and I work with US-English settings.
I leave the frontend the task to format dates accordingly.
-- Gianluca Sartori
August 11, 2009 at 7:17 am
This is the situation I'd like to reach....
select convert(datetime,'31/12/2009',120) --WORKS
select convert(datetime,'31/12/2009') --WORKS
select convert(datetime,'2009-06-30',120) --WORKS
select convert(datetime,'2009-06-30') --WORKS
SELECT isdate('31/12/2009') -- Returns True
SELECT isdate('2009-06-30') -- Returns True
😉
August 11, 2009 at 7:18 am
I don't think there is an easy solution to this without formatting the dates to a known format.
You may find a solution that appears to work, as SQL Server knows there are only 12 months in the year, so 13/12/2009 or 12/13/2009 will be correctly identified, but...
Regardless of how you configure the language settings, if you are going to allow the dates to be in any format (British or American) how is SQL Server going to know if 01/02/2009 is 1st February or 2nd January.
August 11, 2009 at 7:21 am
Try this....
SELECT FORMAT(column_name,format()) FROM table_name:-)
August 11, 2009 at 7:31 am
I’m sorry, but I do think that this is not logical to expect that the server will get all formats of dates and interpret all of them correctly. I think that it is the programmer responsibility to make sure that the server will be able to understand the date that is being sent to the server. This could be done by few ways. The best way is to use a format that will always be interpret correctly by the server regardless of the session/login/OS language and date settings (format 112 is one of those formats). It can also be done by specifying the date format for the session with set dateformat statement.
The reason that I think that it is up to the programmer is that there are things that are not in the control of the DBAs. For example, if there is one database that has few applications that work with it and all of them are using windows authentication. What would happen if each programmer will use different date format (for example programmer A is using the mm/dd/yy format. Programmer B is using dd/mm/yy format and programmer C is using dd-mm-yy format)? No matter what the DBA will do, he won’t be able to solve the problem for all 3 applications. The DBA might be able to change some configuration options (for example default language for the login) and solve the problem for one application, but the other 2 applications will still have a problem with the dates.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 11, 2009 at 7:36 am
I completely agree with you, Adi.
-- Gianluca Sartori
August 11, 2009 at 7:43 am
I've gotta agree with some of the other posters here. You'll never find a format that will work all of the time, the 1/2/09 example is just one. Is it Jan 2 or Feb 1?
You stated that the dates will be coming from numerous applications. It seems like it would be most appropriate to write a separate import routine for each set of incoming data. More than likely there will be other things that are different in the data structures you are importing forcing you to write separate routines anyhow.
If you are getting the data from 4 different sources, you might have 4 different routines, but at least you will know what format source 1 sends, and how it compares to what you are currently using. Once you know source 1's format you'll easily be able to convert it (if needed) to get it into your database correctly.
I've never seen a really good catchall solution, you just need to know your data and convert accordingly. If that means separate import procedures/processes, so be it. at least you know the data will be correct.
-Luke.
August 11, 2009 at 7:44 am
I agree totally with all that, but why so that my servers can not interpret SELECT isdate('2009-06-30') as a valid date?
And surely these should both work if 1 works?
select convert(datetime,'31/12/2009',120) --FAILS
select convert(datetime,'31/12/2009') --WORKS
select convert(datetime,'2009-06-30',120) --WORKS
select convert(datetime,'2009-06-30') --FAILS
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply