CODEWITHSUNDEEP
sqlsql-serverdatebetweendate-range
EJF
EJF

Reputation: 461

Trouble pulling records from a certain date range

I'm trying to pull data from a table that deals with visits to a certain location. Visitors arrive and upon check-in, a Visit Date is created. I need to pull a list of all the visits that took place between July 1, 2015 and December 31, 2015. Seems simple enough, right?

Here's my query:

SELECT VisitorID, VisitorName, VisitDate
FROM VisitList
WHERE VisitDate BETWEEN '07/01/2015' AND '12/31/2015'

(Note: I'm treating those dates as strings because the VisitDate field in this table has a varchar data type for some ridiculous reason -- I don't know who set it up that way but I've had to deal with this on several different occasions.)

The issue is that I'm getting all the right results, but I'm getting results for July-December of 2014 as well, and I only want 2015. What could be causing that, and how can I fix it?

I also tried casting the VisitDate field as a DATE data type and got rid of the quotes around the dates in the WHERE clause, but to no avail.

UPDATE: Here's the query with the Cast:

SELECT VisitorID, VisitorName, CAST(VisitDate AS Date) as Visit_Date
FROM VisitList
WHERE VisitDate BETWEEN 07/01/2015 AND 12/31/2015

Upvotes: 1

Views: 112

Answers (2)

Akshey Bhat
Akshey Bhat

Reputation: 8545

SELECT VisitorID, VisitorName, VisitDate FROM VisitList WHERE CAST(VisitDate As date) between CAST('07/01/2015' as date) and CAST('12/31/2015' as date)

You need to cast varchar

values to type date before comparison.

Upvotes: 1

user5563910
user5563910

Reputation:

User STR_TO_DATE: 

SELECT VisitorID, VisitorName, VisitDate
FROM VisitList
WHERE STR_TO_DATE(VisitDate , '%d/%m/%Y') BETWEEN str_to_date('07/01/2015', '%m/%d/%Y') AND str_to_date('12/31/2015', '%m/%d/%Y')

Upvotes: 0

Related Questions