Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server Parameter Dates Syntax Not Working

Posted on 2013-01-23
7
Medium Priority
?
60 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
[X]
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
7 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 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 500 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 500 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

609 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