CODEWITHSUNDEEP
sqlsql-serverdatecasting
asmgx
asmgx

Reputation: 8054

Converting varchar to date

I am using SQL Server

I have a table that has date field set at varchar and it has the date there in different format

Example 

OldDateOfBith
23/05/1990
4/6/1985
2001-01-06

I need to convert this field into a new field of type datetime

NewDateOfBith
1990-05-23
1985-06-04
2001-01-06

I can convert using 

Cast(OldDateofBirth as datetime) -- will give error for 23/05/1990
CONVERT(DateTime, OldDateofBirth, 103) -- will give error for 2001-01-06

is there a way to convert all dates regardless of the format?

Upvotes: 1

Views: 1734

Answers (4)

Kadir ER
Kadir ER

Reputation: 1

Please use below query:

SET DATEFORMAT dmy;
SELECT TRY_CAST ( OldDateofBirth AS datetime) as newbirddate from...

Upvotes: 0

Meow Meow
Meow Meow

Reputation: 676

Use TRY_CAST() and TRY_CONVERT functions. It's the best and easiest way to resolve your issue:

coalesce(TRY_CAST(OldDateofBirth as datetime),TRY_CONVERT(DateTime, OldDateofBirth, 103));

For fast check you can execute:

select coalesce(TRY_CAST('2001-01-06' as datetime), TRY_CONVERT(DateTime, '2001-01-06', 103));

Documentation links:

Upvotes: 1

Evandro de Paula
Evandro de Paula

Reputation: 2642

Find below an idea only for the formats posted on the question:

--- Query Data ---

INSERT INTO dbo.Test (OldDateOfBith) VALUES
('23/05/1990'),
('4/6/1985'),
('2001-01-06')

--- Query ---

SELECT
    CASE
        WHEN OldDateOfBith LIKE '____-__-__' THEN CONVERT(DATETIME2, OldDateOfBith, 120)
        ELSE CONVERT(DATETIME2, OldDateOfBith, 103)
    END AS NewDateOfBith
FROM
    dbo.Test

The query above was tested on the following version of SQL Server only:

Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64)   Jul  5 2017 23:41:17   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Home 6.3 <X64> (Build 17134: )

Having that said, you may need to run this in multiple steps.

Upvotes: 1

Danushka Amith Weerasingha
Danushka Amith Weerasingha

Reputation: 115

Since you don't know the exact date format of the string date it is impossible to convert

As a example '12/12/2018' In here you don't know which is day and which is month

Upvotes: 1

Related Questions