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

​x
 
Client    Serial#        Hrs        MyDateA         1              12         20200501A         1              8          20200513B         5              2          20200521B         6              3          20200522A         2              5          20200528A         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#

 
SELECT Client, Serial#, SUM(Hrs)FROM MyTableWHERE 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

Answer

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)

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

Advertisement

Answer