4

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
user2145047
  • 73
  • 1
  • 1
  • 6
  • There is similar question. Please see one of [suggestion](https://stackoverflow.com/questions/20838344/sql-the-conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in/38081347#38081347) that can be done. – Sumit Jun 28 '16 at 16:24

1 Answers1

17

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;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanks - understand the theory on dates and you are quite right. However lets assume in this instance this isn't possible. – user2145047 Mar 07 '13 at 16:53
  • 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 Mar 07 '13 at 16:54
  • Hi - when I press enter it adds the comment - appreciate the time spent! – user2145047 Mar 07 '13 at 16:55
  • @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. – Aaron Bertrand Mar 07 '13 at 16:56
  • Thanks - here is what I am attempting: – user2145047 Mar 07 '13 at 17:05
  • Select CONVERT(DateTime,(CONVERT(varchar, CONVERT(DateTime,'25/12/2013 00:00'), 103) + ' 00:00')) – user2145047 Mar 07 '13 at 17:05
  • The value is coming from a table - I have added as a static string value for ease – user2145047 Mar 07 '13 at 17:06
  • 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);`? – Aaron Bertrand Mar 07 '13 at 17:06
  • 1
    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. – Jimbo Nov 22 '18 at 09:25
  • 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... – Geoff Griswald Oct 08 '19 at 13:10