Out of range data conversion error

Question

Dear Experts,

I am getting this error on one of the SQL jobs.

declare @month int=null
Set @month=12
select * from Table_A where CAST(CONVERT(datetime, TableLocalDate, 131) AS DATE) <=DATEADD(MONTH,@Month*-1, GETDATE())

"The conversion of a varchar data type to a date time data type resulted in an out-of-range value"

The TableLocalDate is of varchar data type

Could you please advise how this query can be fixed?

Answer 1

The reason you are getting this that one or more rows have data that is not a valid type 131 date. How you fix it depends on what you want done. If you want to find all the rows with bad data so that you can fix them you can do

select * from @T where CAST(TRY_CONVERT(datetime, TableLocalDate, 131) AS DATE) Is NULL And TableLocalDate Is Not NULL

That will show you the rows with bad data. You can then correct the data.

If, instead, you want to have SQL ignore any rows with bad TableLocalDate values, you can do
select * from @T where CAST(TRY_CONVERT(datetime, TableLocalDate, 131) AS DATE) <=DATEADD(MONTH,@Month-1, GETDATE())

If you do that you won't get any errors and none of the rows which would have caused the error will be in your result.

Tom

Answer 2

Hi，@mo boy

Welcome to Microsoft T-SQL Q&A Forum!

Generally, this problem occurs because the format of the two filter dates before and after does not match. Please make sure to use mm/dd/yyyy or use dd/mm/yyyy format to filter dates, whichever is applicable in your database. It is recommended You provide us with DDL statements in order to better help you solve the problem.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.