question

moboy-9601 avatar image
0 Votes"
moboy-9601 asked BertZhoumsft-7490 edited

Out of range data conversion error

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?

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BertZhoumsft-7490 avatar image
0 Votes"
BertZhoumsft-7490 answered BertZhoumsft-7490 edited

Hi,@moboy-9601

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.








5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.