SQL Query - between dates.

Question

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

Answer 1

0

If you change the date formats to: 28-04-2011 to '28-apr-2011' and '28112011' to '28-nov-2011'

it works fine.

answered Aug 01 '11 at 12:07 AM

Mrs_Fatherjack
4.6k ● 43 ● 52 ● 60

(comments are locked)

Answer 2

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 <std_disclaimer_about_using_Wikipedia_as_authoritative_source>

Answer 3

0

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

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)

answered Aug 01 '11 at 12:26 AM

Dave_Green
1.6k ● 1 ● 4

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.

Aug 01 '11 at 01:20 AM WilliamD

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.

Aug 01 '11 at 05:43 AM Dave_Green

(comments are locked)

Answer 4

0

SELECT * FROM [I have table name here] WHERE Convert(date,EFFECTIVE_TRANSACTION_DATE BETWEEN,110) between '28-04-2011' AND '28112011';

answered Aug 01 '11 at 12:25 AM

SQLHeaven
1

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

Aug 01 '11 at 01:18 AM WilliamD

What if my sql server has 50 database and some database use USA date format and some use Indian date format?

Aug 01 '11 at 01:36 AM SQLHeaven

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.

Aug 01 '11 at 01:59 AM WilliamD

This is precisely the sort of problem that following the ISO standard format will get you out of.

Aug 01 '11 at 02:01 AM ThomasRushton ♦

(comments are locked)

SQL Query - between dates.

Follow this question

Related Questions