Solved

SQL Server Parameter Dates Syntax Not Working

Posted on 2013-01-23
7
41 Views
Last Modified: 2016-07-04
Good Day:

I am using SQL Server 2008 R2.  I am trying to query dates between 01/01/2009 and 10/31/2011. The following syntax is not working.  I get all dates instead of my where clause dates.

SELECT     mem_code, CONVERT(VARCHAR(10), mem_canceldate, 101) AS CancelDate
FROM         dbo.t_Members_All_Data
WHERE     (CONVERT(VARCHAR(10), mem_canceldate, 101) BETWEEN '01/01/2009' AND '10/31/2011')

Output:
mem_code      CancelDate
1234            07/01/1994
5678            02/01/2008
9999            01/01/2013
8888            04/01/2003
7777            06/01/2011

Any Suggestions?

Thank You,
Denise
0
Comment
Question by:DeniseGoodheart
7 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 total points
ID: 38812709
That is because you are treating them as strings and not dates.  It should be:
SELECT  mem_code,
        CONVERT(VARCHAR(10), mem_canceldate, 101) AS CancelDate
FROM    dbo.t_Members_All_Data
WHERE   mem_canceldate BETWEEN '20090101' AND '20111031'

Open in new window

0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 125 total points
ID: 38813280
For clarity, I've always been a fan of format 120 ("2013-01-23") as it sorts and filters properly, even when stored as a string instead of a date. That said, you're seeing this behavior because it's treating the dates like strings, not the date data type (you'll notice you don't have anything in your result set from November or December, even if the year would include it in your range).

It's worth noting that, though I can't tell from your code, if the mem_canceldate is already a DATETIME, there's no conversion necessary at all - you can leave it as a DATETIME and your filtering would work exactly as you expect it to. As an example, in the following example, it returns true, even though the date (as a string) is outside the range of the filters (if they were strings), but is inside the filter as a date:

DECLARE @test DATETIME
SET @test = '2012-08-15'

SELECT convert(varchar(10), @test, 101),
       case when @test between '02/01/2012' and '05/01/2013'
	        then 'Yes' else 'No' end

Open in new window


To filter it properly, you can either leave it as a DATETIME if it already is, or you can explicitly convert it to a DATETIME:

SELECT     mem_code, CONVERT(VARCHAR(10), mem_canceldate, 101) AS CancelDate
FROM         dbo.t_Members_All_Data
WHERE     CONVERT(DATETIME, mem_canceldate) BETWEEN '01/01/2009' AND '10/31/2011'

Open in new window

0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 125 total points
ID: 38813394
I confirm above, you may want to read up this article to avoid this error in future:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 125 total points
ID: 38814634
Looks like you do have a "date" data type in the table.

For guaranteed accuracy with any date or datetime or datetime2 data type, I suggest always using a >= and < comparison.  That way, even if the data type changes later, your code continues to compare correctly.

For example:
WHERE mem_canceldate >=  '20090101' AND mem_canceldate < '20111031'

That works accurately whether mem_canceldate is date, datetime or datetime2.


Another example, to select all rows for a single hour, 4PM, of a single day:

WHERE mem_canceldate >=  '20111031 16:00' AND mem_canceldate < ''20111031 17:00'

Note that a "<=" or BETWEEN is extremely tricky to write correctly, but a < is not.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 41688062
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now