Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Server Parameter Dates Syntax Not Working

Posted on 2013-01-23
7
55 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 143

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

839 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