Solved

SQL Server Parameter Dates Syntax Not Working

Posted on 2013-01-23
7
52 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:Scott Pletcher
Scott Pletcher 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

785 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