SQL: Counting dates formatted as varchar

Question

The task:

I need to count every record in a table that was added after 07/01/2011.

Info:

There is a column in the table "Date_added" which is formatted as varchar and contains dates in format mm/dd/yyyy.

What I've tried:

I tried the following query:

SELECT count(date_added) FROM Abatements_Doc WHERE date_added >= '07/01/2011'

Unfortunately it counted any date where the month was greater than 07 regardless of the year or day. I'm at a loss as to how I should format my query to get it to read the dates properly instead of by ascii sort. In my research I discovered the CAST and CONVERT options, but am not sure how to use them, or which one I should use if any. I'm extremely new to SQL, and want to make sure I don't mess up the data in the table. I just need to get the count as specified above without altering the data in the table in any way. Any help in this regard will be very much appreciated. Thanks in advance.

Why are you storing dates as varchar? http://stackoverflow.com/a/4759039/284240 — Tim Schmelter, Feb 14 '12 at 18:15
The database is part of a web app we purchased from a vendor. I've been cursing them over that same question all morning. — rhuarch, Feb 14 '12 at 19:29

score 2 · Accepted Answer · edited Feb 14 '12 at 18:21

2

I didn't test. But try convert statement.

SELECT count(date_added) 
FROM Abatements_Doc 
WHERE convert(datetime,date_added,1) >= convert(datetime,'07/01/2011',1)

edited Feb 14 '12 at 18:21

Lamak

69,480
12
108
116

answered Feb 14 '12 at 18:16

groovekiller

1,122
2
8
20

score 1 · Answer 2 · answered Feb 14 '12 at 19:19

(1) don't use mm/dd/yyyy as your date format. Use a safe and unambiguous format; the only one I trust for DATETIME/SMALLDATETIME is YYYYMMDD.

(2) change the data type in your table to DATETIME/SMALLDATETIME/DATE depending on accuracy required. Then `WHERE date_added >= '20110701' will work just fine (and will use an index on that column, if one exists).

(3) if you can't change the data type, then the following will work just fine as well (though no index will be used):

WHERE CONVERT(DATETIME, date_added, 101) >= '20110701'

score 0 · Answer 3 · answered Feb 14 '12 at 18:15

0

Varchar can't be compared, try changing your column to date type.

answered Feb 14 '12 at 18:15

woopata

875
5
17
29

1

Well, to be pedantic, varchar can be compared, it just can't be compared accurately if dates are stored in this format. – Aaron Bertrand Feb 14 '12 at 19:21

score 0 · Answer 4 · answered Feb 14 '12 at 18:20

0

Convert both date_added column values and your target date to DATETIME and compare; as in:

SELECT COUNT(1)
FROM Abatements_Doc
WHERE (CONVERT(datetime, date_added, 101)) >= CONVERT('07/01/2011', date_added, 101))

answered Feb 14 '12 at 18:20

Web User

7,438
14
64
92

You don't need to convert the right-hand side (even if the syntax was tested and correct). – Aaron Bertrand Feb 14 '12 at 19:20

SQL: Counting dates formatted as varchar

4 Answers4