?
Solved

SQL Server Parameter Dates Syntax Not Working

Posted on 2013-01-23
7
Medium Priority
?
58 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 69

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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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 insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

771 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