December 17, 2009 at 5:12 am
Dear Friends
I have my server Collation as latin1_general_ci_as.
My database is also the above collation.
When i query
select * from table where date between '2009-12-16 00:00:00' and '2009-12-17 00:00:00'.
I get an error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
It works fine if put the day <=12.
My default language is British English. The login is using British English as well.
do i need to change the collation settings to sql_latin1_general_cp1_ci_as? I thought this was just for backward compatibility.
Please help?
many thanks.
December 17, 2009 at 6:49 am
Try this:
WHERE date >= convert(datetime, '2009-12-16 00:00:00.000, 121', 121) and date < convert(datetime, '2009-12-17 00:00:00.000', 121)
December 17, 2009 at 7:18 am
I am sorry i didn't explain it properly. What i meant was the query works fine when running it against the UK customer who have the collation as sql_latin1_general_cp1_ci_as, but doesn't when run against one of the UK customer who have the collation as latin1_general_cp1_ci_as.
Why is it happening on latin1_general_cp1_ci_as. Why is the date format not working for this collation.
But when i run select getdate() on the database where it's failing it returns in the format yyyy-mm-dd hh:mm:ss which is what ia m using in the query in my question.
December 17, 2009 at 7:21 am
Did you try what I suggested? Did it work?
December 17, 2009 at 7:37 am
No it didn't work. I get the same error message.
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
December 17, 2009 at 7:44 am
Lynn Pettis (12/17/2009)
Try this:WHERE date >= convert(datetime, '2009-12-16 00:00:00.000', 121) and date < convert(datetime, '2009-12-17 00:00:00.000', 121)
Noticed a problem in the code I had posted. Is this what you tried earlier? If not, please try again.
December 17, 2009 at 7:49 am
Yes, it works with the amended query.
But why do i have to use convert for the collation latin1_general_cp1_ci_as and not with SQl1_latin1_general_cp1_ci_as?
December 17, 2009 at 7:58 am
You have two different clients from what I can tell. One works, one doesn't. I don't think it is the collation, but another setting. The reason I think this, is that when you use 2009-12-12 or less, it works. This tells me that SQL is not converting the date properly. Using the explicit conversion using convert and the 121 (which tell convert the format of the character date/time value) is working.
Try this before your original query:
set dateformat ymd
December 17, 2009 at 8:02 am
Hi lynn
Yes that will work,, but i will have to change all the Standard reports with : set dateformat ymd
There's 100's of reports..
Is there a setting somewhere which is incorrect??
December 17, 2009 at 8:17 am
Okay, I am really having troubles this morning thinking.
How are the users entering the dates?
December 17, 2009 at 8:24 am
Through the parameters in the lookup from the reprts..
December 17, 2009 at 8:30 am
Run the dates in the reports through custom code that converts to a universal time format which would be yyyymmdd hh(24):mm:ss. Jamie Thompson had a good T-SQL Tuesday blog post about this exact issue.
Obviously the issue is that your client has a different Language setting.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 17, 2009 at 9:21 am
Date format is login specific
British English is MM/DD/YYYY and YYYY-DD-MM format
English (us_english) is DD/MM/YYYY and YYYY-MM-DD format
Run this code and you will get failed for British English
SET LANGUAGE N'us_english'
PRINT @@LANGUAGE
BEGIN TRY
PRINT CAST(CAST('2009-12-16 00:00:00' as datetime) as varchar)+' '+CAST(CAST('2009-12-17 00:00:00' as datetime) as varchar)
END TRY
BEGIN CATCH
PRINT 'Failed'
END CATCH
SET LANGUAGE N'English'
PRINT @@LANGUAGE
BEGIN TRY
PRINT CAST(CAST('2009-12-16 00:00:00' as datetime) as varchar)+' '+CAST(CAST('2009-12-17 00:00:00' as datetime) as varchar)
END TRY
BEGIN CATCH
PRINT 'Failed'
END CATCH
SET LANGUAGE N'British'
PRINT @@LANGUAGE
BEGIN TRY
PRINT CAST(CAST('2009-12-16 00:00:00' as datetime) as varchar)+' '+CAST(CAST('2009-12-17 00:00:00' as datetime) as varchar)
END TRY
BEGIN CATCH
PRINT 'Failed'
END CATCH
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply