CODEWITHSUNDEEP
sqlsql-server
Conner
Conner

Reputation: 307

Converting Date in Where clause in SQL

I need to return the sum of something within the last 30 days. My date field is a text field. My table looks something like this:

Client    Serial#        Hrs        MyDate
A         1              12         20200501
A         1              8          20200513
B         5              2          20200521
B         6              3          20200522
A         2              5          20200528
A         2              2          20200529

my Code looks like this:

SELECT Client, Serial#, SUM(Hrs)
FROM MyTable
WHERE CONVERT(DATETIME, MyDate, 112) > DATEADD(day, -30, getdate())
GROUP BY Client, Serial#

This is the error I get "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

I tried removing the convert function and got another error: "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

So now I am stuck. Thanks in advance for the help

Upvotes: 0

Views: 5486

Answers (4)

Wouter
Wouter

Reputation: 2976

Because you have no other choice then to use a varchar as a date, then I would convert the one GETDATE() value to a varchar instead of converting all the rows to a date. It's possible because they are stored as YYYYMMDD. I think that would be bettter for performance (for sure if you have an index on this date column). 

WHERE MyDate > CONVERT(varchar(10),dateadd(d,-30,getdate()),112)

Upvotes: 0

Jon Wilson
Jon Wilson

Reputation: 776

Your convert function looks ok (although the 112 is unneeded going to datetime, that is for datetime to string conversion.) So I bet you have some bad data somewhere in those date strings. Dates have to be between January 1, 1753 - December 31, 9999. Try sorting on that field and see if there is anything at the top or bottom of the list that looks suspicious.

You could add a filter:

    AND MyDate BETWEEN '17530101' and '99991231'
    AND LEN(MyDate) = 8

Upvotes: 0

Keith Harris
Keith Harris

Reputation: 1148

SELECT Client, Serial, SUM(Hrs)
FROM MyTable
WHERE CONVERT(DATETIME, MyDate) > DATEADD(day, -30, getdate())
GROUP BY Client, Serial

You don't need the format 112 for comparison

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can fix this using try_convert():

WHERE TRY_CONVERT(DATE, MyDate) > DATEADD(day, -30, getdate())

Your format is the SQL Server defined format for a date constant, so you don't really need the format argument.

You can find the offending values using:

select mydate
from t
where try_convert(date, mydate) is null and mydate is not null;

Note that dates and datetimes have different ranges, so just using date instead might fix the problem.

Upvotes: 1

Related Questions