Converting UK format date to datetime

Question

I have a value in SQL that represents a date, but it of type nvarchar. The value of the date is in the format:

dd/mm/yyyy hh:mm

I need to present this column via a view to a CCure 800 database in DATETIME format. I expected to use CAST/CONVERT; however, when using this, the following error is returned:

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Solution

Please stop storing dates as strings, and especially as Unicode strings. Are you concerned that some future version of our calendar will have umlauts, pound signs, hieroglyphics, or Mandarin instead of numbers?

Store dates as dates. That is what those data types are for. In addition to not having to worry about invalid interpretations, you also get all of the benefits of things like DATEPART and DATEADD, and you don't have to worry about anyone stuffing nonsense into the column (anything from 31/02/2012 to 'I don''t want to enter a real date'...

In the meantime, you just need to use a style number with CONVERT (this won't work reliably with CAST):

SELECT CONVERT(DATETIME, '13/06/2013 09:32', 103);

To make it work with CAST, you could set your LANGUAGE or DATEFORMAT settings accordingly, but you can't do this inside a view, and it makes the code very brittle anyhow IMHO.

SET DATEFORMAT DMY;
SELECT CAST('13/06/2013 09:32' AS DATETIME);

Or

SET LANGUAGE BRITISH;
SELECT CAST('13/06/2013 09:32' AS DATETIME);

By a very wide margin, I prefer the additional control CONVERT gives you, and almost unilaterally change all instances of CAST to CONVERT, even when this control is not needed, for consistency (why use CAST in some cases, and CONVERT when you need it, when you can just use CONVERT always?).

EDIT

To identify the garbage data that has snuck into your table because of a bad data type choice, do this (and then fix the data or, better yet, fix the data type so this doesn't happen again):

SET DATEFORMAT DMY;
SELECT date_column FROM dbo.table_name WHERE ISDATE(date_column) = 0;

Comments

[user2145047]

Thanks - understand the theory on dates and you are quite right. However lets assume in this instance this isn't possible.

[Adir D]

@user2145047 well, I still provided an answer. Read from "In the meantime" on...

[user2145047]

I tried using CONVERT also (as well as CAST) but the key to the issue is that it won't work when the format of the string is exactly "DD/MM/YYYY HH:MM" - CONVERT wont convert this into a date.

[user2145047]

Hi - when I press enter it adds the comment - appreciate the time spent!

[Adir D]

@user2145047 can you show an example of the exact CONVERT command you tried, and what the actual value was instead of just saying DD/MM/YYYY? Maybe you aren't using CONVERT correctly, or maybe you have stored garbage data.

[user2145047]

Thanks - here is what I am attempting:

[user2145047]

Select CONVERT(DateTime,(CONVERT(varchar, CONVERT(DateTime,'25/12/2013 00:00'), 103) + ' 00:00'))

[user2145047]

The value is coming from a table - I have added as a static string value for ease

[Adir D]

That seems to work fine for me, but why do you need three converts, and why are you adding ' 00:00'? Why not just: SELECT CONVERT(DATETIME, '25/12/2013 00:00', 103);?

[Adir D]

Then my guess (once again) is that you have garbage data in your table!

[Jimbo]

There are legitemate circumstances where a date might be in a string format. During data migration, for instance or importing data from a csv file.

[Geoff Griswald]

Indeed, Aaron's answer is needlessly combative. I am about to attempt something similar and none of the approaches here actually work. I suspect the real answer might be rather more tedious, I am going to approach the problem with a combination of DATEFROMPARTS, splitting the string out using SUBSTRING on the "/" character and copious amounts of LEFT and RIGHT operators...