1

Insert execution block

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
  • Can you share the sample data of Gradd19 value ? – Arulkumar Sep 27 '16 at 6:33
  • sample data of gradd19 column....... Sep 16 2016 12:00AM Sep 16 2016 12:00AM Sep 16 2016 12:00AM Sep 19 2016 12:00AM 20-09-2016 Sep 16 2016 12:00AM Sep 20 2016 12:00AM – user106763 Sep 27 '16 at 9:27
1

Since you are not shared the sample data of the Gradd19 value, I suspect the date value is in the yyyy-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:

DECLARE @FromDate DATETIME;
DECLARE @Gradd19 AS VARCHAR (20) = '2016-11-15';
SELECT @FromDate = CONVERT(VARCHAR, @Gradd19, 105);
SELECT @FromDate;

Incase if it is in yyyy-dd-mm it will throw out-of-range value error

DECLARE @FromDate1 DATETIME;
DECLARE @Gradd191 AS VARCHAR (20) = '2016-15-11';
SELECT @FromDate1 = CONVERT(VARCHAR, @Gradd191, 105); 
SELECT @FromDate1;

So in that case you need do one more conversion to skip the issue, like the below:

DECLARE @FromDate2 DATETIME;
DECLARE @Gradd192 AS VARCHAR (20) = '2016-15-11';
SELECT @FromDate2 = CONVERT(VARCHAR(19), CONVERT(DATETIME, @Gradd192, 105), 120)
SELECT @FromDate2;

So instead of

CONVERT(VARCHAR, Gradd19, 105) AS ExpiryDate

you need to use

CONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120) AS ExpiryDate

Update: As you mentioned in the comments the Gradd19 having the values of 20 and 60, it is difficult to filter those non-valid data. But based on the LEN() 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:

DECLARE @GradedDETAILS  TABLE (Gradd19 VARCHAR (30));

INSERT INTO @GradedDETAILS (Gradd19) VALUES
('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');

SELECT CONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120)
FROM @GradedDETAILS
WHERE LEN(Gradd19) >= 10

So in your actual query also you need to add the LEN(Gradd19) >= 10 condition and in the SELECT use this to convert as DATETIME by CONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120)

  • Gradd19 sample data 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 – user106763 Sep 27 '16 at 8:54
  • Is it Gradd19 column contain 20, 60 also one of the value ? – Arulkumar Sep 27 '16 at 9:20
  • ya...20 and 60 also values of gradd19 column – user106763 Sep 27 '16 at 9:25
  • @user106763 Please check Update: block in the answer above. – Arulkumar Sep 27 '16 at 10:24
0

I'm not sure if this is your problem, but - DateTime does not support anything less than '1753-01-01'. Values less than this will generate the error you're seeing. You might need to apply a CASE expression to evaluate that condition and force '1753-01-01' (or NULL depending on your situation) if the original value is less.

Example:

CASE 
    WHEN <TableColumn> < '1753-01-01' THEN '1753-01-01' 
    ELSE <TableColumn> 
END AS <TableColumn>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.