Converting UK format date to datetime

问题:

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.

回答1:

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;