CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
Michael Horton
Michael Horton

Reputation: 23

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. Error

I create a temp table with the following:

CREATE TABLE #temp_conv_data 
(
    company_code VARCHAR(4),  
    source_currency VARCHAR(3), 
    effective_date DATETIME, 
    conversion_factor NVARCHAR(20), 
    create_time DATETIME, 
    revision_time DATETIME,
    from_web_services BIT
)

Then, I try to populate it with insert statements like the one below and get the error:

INSERT INTO #temp_conv_data (company_code, source_currency, effective_date, conversion_factor, create_time, revision_time, from_web_services) 
VALUES ('FGLS', 'ADP', '2021-01-20 18:00:00',  0.00729, '2020-17-08 02:15:53', '2020-17-08 02:15:53', 0)

I tried using CAST('2021-01-20 18:00:00' AS DATETIME) and CONVERT('2021-01-20 18:00:00', 126) on the date values and got the same error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Upvotes: 1

Views: 205

Answers (2)

dhiman
dhiman

Reputation: 517

You have to use yyyy-mm-dd you are using 2020-17-08 (yyyy-dd-mm).

Upvotes: 0

Greg Low
Greg Low

Reputation: 1586

When you are inserting date and time values into SQL Server, you should always write your date strings in a region-neutral format.

INSERT INTO #temp_conv_data (company_code, source_currency, effective_date, conversion_factor, create_time, revision_time, from_web_services) 
VALUES ('FGLS', 'ADP', '20210120 18:00:00',  0.00729, '20200817 02:15:53', '20200817 02:15:53', 0)

Note the complete lack of slashes and dashes. This is the only format that works reliably on all SQL Server date and time related types, without worrying about region settings, apart from using the full ISO8601T format. If you use that format, it has to be the whole format, including the T. But the format I've suggested above is the easiest.

Note that as Nick mentioned, you also used month/day in different order in different constants. That was never going to work, regardless of regional setting.

Upvotes: 1

Related Questions