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

asked Jul 31, 2011 at 09:02 PM in Default

ratzy gravatar image

65 6 6 8

Thanks everyone

used this and it works :)

SELECT * FROM [table name] WHERE EFFECTIVE_TRANSACTION_DATE BETWEEN '28-apr-2011' AND '28-apr-2011'
Aug 01, 2011 at 02:21 PM ratzy
(comments are locked)
10|1200 characters needed characters left

5 answers: sort newest

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

it works fine.
more ▼

answered Aug 01, 2011 at 12:07 AM

Mrs_Fatherjack gravatar image

4.7k 60 62 66

(comments are locked)
10|1200 characters needed characters left

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>
more ▼

answered Aug 01, 2011 at 01:08 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

(comments are locked)
10|1200 characters needed characters left

I suspect you have a problem from SQL Server interpreting your dates in MM-DD-YYYY format - suggest you use


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][2])

[2]: http://msdn.microsoft.com/en-us/library/ms180878.aspx#UnseparatedStringFormat
more ▼

answered Aug 01, 2011 at 12:26 AM

Dave_Green gravatar image

Dave_Green ♦
4.2k 3 4 8

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, 2011 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, 2011 at 05:43 AM Dave_Green ♦
(comments are locked)
10|1200 characters needed characters left
SELECT * FROM [I have table name here] WHERE Convert(date,EFFECTIVE_TRANSACTION_DATE BETWEEN,110) between '28-04-2011' AND '28112011';
more ▼

answered Aug 01, 2011 at 12:25 AM

SQLHeaven gravatar image


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, 2011 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, 2011 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, 2011 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, 2011 at 02:01 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 31, 2011 at 09:02 PM

Seen: 1413 times

Last Updated: Aug 01, 2011 at 12:25 PM