question

ratzy avatar image
ratzy asked

SQL Query - between dates.

I am trying to show all data in my table that is between certain dates. When I enter the following i get the error: Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. SELECT * FROM [I have table name here] WHERE EFFECTIVE_TRANSACTION_DATE BETWEEN '28-04-2011' AND '28112011'; Can someone please help :)
t-sqlsql-serverdateformat
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks everyone used this and it works :) SELECT * FROM [table name] WHERE EFFECTIVE_TRANSACTION_DATE BETWEEN '28-apr-2011' AND '28-apr-2011'
0 Likes 0 ·
Mrs_Fatherjack avatar image
Mrs_Fatherjack answered
If you change the date formats to: 28-04-2011 to '28-apr-2011' and '28112011' to '28-nov-2011' it works fine.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SQLHeaven avatar image
SQLHeaven answered
SELECT * FROM [I have table name here] WHERE Convert(date,EFFECTIVE_TRANSACTION_DATE BETWEEN,110) between '28-04-2011' AND '28112011';
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This is precisely the sort of problem that following the ISO standard format will get you out of.
3 Likes 3 ·
Then follow the advice of the other answers here and change the format that is being passed in. SQL Server deals with `YYYYMMDD` fine, no matter what DATEFORMAT setting is used on the system.
2 Likes 2 ·
Converting the way you suggest removes SARG-ability for this query and can have a negative impact on performance. The convert should, if at all, be done on the values passed into the query (the between dates).
1 Like 1 ·
What if my sql server has 50 database and some database use USA date format and some use Indian date format?
0 Likes 0 ·
Dave_Green avatar image
Dave_Green answered
I suspect you have a problem from SQL Server interpreting your dates in MM-DD-YYYY format - suggest you use `SET DATEFORMAT dmy` at the beginning of your query, or alter the server if it's a regular issue. Info on SET DATEFORMAT can be found [here][1] I also note (and this will almost certainly be your main issue) that you are using an unseparated format - The **six-digit or eight-digit strings are always interpreted as ymd**. (see [ http://msdn.microsoft.com/en-us/library/ms180878.aspx#UnseparatedStringFormat][2]) [1]: http://msdn.microsoft.com/en-us/library/ms189491.aspx [2]: http://msdn.microsoft.com/en-us/library/ms180878.aspx#UnseparatedStringFormat
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Altering the DATEFORMAT of the system is a little over the top, not to mention it could cause problems elsewhere in the system. @ratzy should just change from using an ambiguous format to one that always wins (as you suggested in the 2nd half of your answer). +1 for that.
4 Likes 4 ·
Upon reflection - and a fresh cup of coffee - I entirely agree that altering the server setting would be over the top! Thanks for picking me up on that.
3 Likes 3 ·
ThomasRushton avatar image
ThomasRushton answered
I would strongly recommend use of ISO8601 format - it removes this sort of ambiguity entirely, making you specify date/times most significant part first. Hence the following are unambiguous dates: * 2011-08-01 * 2011-06-08 - what do you get in your system when you specify 08/06/2011? 8th June, or 6th August? Either of these are valid interpretations of 08/06/2011 in various parts of the world. To add in the time: * 2011-08-01 09:02 - SQL Server's a bit more flexible than the ISO standard, so this is more readable than the ISO standard's 2011-08-01T09:02Z There's more about it on the appropriate Wikipedia page http://en.wikipedia.org/wiki/ISO_8601 insert
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.