FROM_DATE
column is DATETIME
format in the temptable1stockdetails
table.
Gradd19
column is VARCHAR
format in the GradedDETAILS
table.
I want to insert the Gradd19
value to FROM_DATE
but it throws the following error:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated
How can I solve this issue.
Sample data of the Gradd19
in the GradedDETAILS
table is:
Sep 16 2016 12:00AM
Sep 16 2016 12:00AM
Sep 16 2016 12:00AM
Sep 19 2016 12:00AM
Sep 19 2016 12:00AM
Sep 16 2016 12:00AM
20
60
20-09-2016
20-09-2016
Best Answer
Since you are not shared the sample data of the
Gradd19
value, I suspect the date value is in theyyyy-dd-mm
format, so when trying to convert (month value more than 12) it cause the out-of-range value error.For sample, if the date format in
yyyy-mm-dd
, it will execute properly:Incase if it is in
yyyy-dd-mm
it will throw out-of-range value errorSo in that case you need do one more conversion to skip the issue, like the below:
So instead of
you need to use
Update: As you mentioned in the comments the
Gradd19
having the values of20
and60
, it is difficult to filter those non-valid data. But based on theLEN()
function you may avoid the non-valid data, then using the CONVERT you can get the VARCAHR field as DATETIME value.Based on your sample data the valid date's minimum length is 10 characters, so you can apply the condition in the
WHERE
clause.Sample execution:
So in your actual query also you need to add the
LEN(Gradd19) >= 10
condition and in theSELECT
use this to convert asDATETIME
byCONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120)