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!

asked 2017-04-06 by Colin

---

Answer

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.

answered 2017-04-06 by Andy Mallon