Convert Varchar field to datetime in SQL Server

Question

I am trying to convert varchar date to date time field and having a really hard time. It is giving me:

Conversion failed when converting date and/or time from character string.

or

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

I have 2 datetime formats below :

Date field looks like 21-12-2009, 05-10-2005 etc
Date field looks like 19-03-2018 14:59

select cast(convert(varchar,Stg_hw.date_of_birth,110)as date)
from Stg_Height_and_Weight Stg_hw
where Stg_hw.person_id = 1620458

select CONVERT(datetime,LEFT(date_of_birth,2)+SUBSTRING(Stg_hw.date_of_birth,4,2)+SUBSTRING(Stg_hw.date_of_birth,7,4)) 
from Stg_Height_and_Weight Stg_hw
where Stg_hw.person_id = 1620458

select cast(Stg_hw.date_of_birth as date)
from Stg_Height_and_Weight Stg_hw
where Stg_hw.person_id = 1620458

Arulkumar Arulkumar · Accepted Answer · 2019-08-29T06:42:53

CONVERT(DATETIME, DateFieldColumnName, 103) can help to convert those 2 format to datetime.

Sample exection:

CREATE TABLE TestTable (DateVal VARCHAR (20));

INSERT INTO TestTable (DateVal) VALUES
('21-12-2009'), ('05-10-2005'), ('19-03-2018 14:59');

SELECT CONVERT(DATETIME, DateVal, 103) AS Result
FROM TestTable

Please find the working demo on db<>fiddle

Convert Varchar field to datetime in SQL Server

1 Answers