Converting date stored as a varchar

Question

I'm creating a query that has a column of dates stored as a VARCHAR in the format DD-MMM-YYYY.

Could you tell me how I can convert this into date format please?

I don't want to alter the original table in any way, as it's not mine to tinker!

Answer 1

You can use CAST to convert your VARCHAR value into DATE format.

Syntax for CAST:

CAST ( expression AS data_type [ ( length ) ] )

---

Sample execution: You can convert into DATE alone or DATETIME format:

DECLARE @TestDate AS VARCHAR (12) = '22-MAY-2015';
SELECT CAST(@TestDate AS DATE);

Output: 2015-05-22

DECLARE @TestDate1 AS VARCHAR (12) = '22-MAY-2015';
SELECT CAST(@TestDate1 AS DATETIME);

Output: 2015-05-22 00:00:00.000

Answer 2

You should use CONVERT when converting between date/datetime and varchar datatypes.

Relying on implicit conversion for date formats can give unexpected results.

By using CONVERT you are able to explicitly specify date & time styles for your data. Date format of DD-MMM-YYYY, corresponds to date format 105, so you could use something like this:

DECLARE @TestDate AS VARCHAR (12) = '06-MAR-2017';
SELECT CONVERT(DATE,@TestDate,105);

For additional reasons on why to favor CONVERT over CAST, this article has some more examples.