SQL TRY_CONVERT DateTime vs DateTime2

Question

I have a situation where I need to convert datetime string values in a column to a more standardised format, the converting process is quiet manual I am not just casting and storing.
So the script grabs the value, fumbles it around and stores it back in the same cell in a ODBC format. The thing is that I need to be able to check whether the value stored in the cell has already been converted or still needs to be processed as the script needs to be re-runnable.

So the example situation that I have is I come across the following two values:

08/03/2017 3:51 PM
28/03/2017 7:30 AM

Now I know for a fact that it is day first.

If I use

TRY_CONVERT(DATETIME, [Value], 121)

I will get the resulting value:

2017-08-03 15:51:00.000
NULL

Now I would like to get is NULL for both, so it is correct for the second value but not the first and worse it swapped the day and month components for the first one.

However if I use

TRY_CONVERT(DATETIME2, [Value], 121)

I do get NULL for both note, so my question is what is the difference in converting with DATETIME and DATETIME2? Why does DATETIME2 return NULL for the first?

I know that it is returning NULL for the second value because the first componentt is greater than 12.

Side note:

At the end of my script I end up with the ODBC form for the values (probably should be ISO?)

2016-06-07 03:23:17.000
2016-06-07 03:23:17.000

When I run

TRY_CONVERT(DATETIME2, [Value], 121)

I happily get not NULL values so I don't have to covert it :)

Answer 1

If you need to convert day first into ODBC canonical (with milliseconds), you can use set dateformat dmy and try_convert(datatype,val) without the style component.

Apply the 121 style after the conversion to datetime/datetime2 when converting back to char() for string storage.

create table t (dt varchar(32));
insert into t values ('08/03/2017 3:51 PM'),('28/03/2017 7:30 AM');

set dateformat dmy;
select 
    dt
  , tryparse      = convert(char(23),try_parse(dt as datetime2(3)),121) 
  , tryparseBrit  = convert(char(23),try_parse(dt as datetime2(3) using 'en-GB'),121) 
  , tryconvert    = convert(char(23),try_convert(datetime2(3),dt),121) 
  , tryconvert121 = convert(char(23),try_convert(datetime2(3),dt,121),121) 
  , trycast       = convert(char(23),try_cast(dt as datetime2(3)),121)
from t

rextester demo: http://rextester.com/GFC92046

returns:

+--------------------+-------------------------+-------------------------+-------------------------+---------------+-------------------------+
|         dt         |        tryparse         |      tryparseBrit       |       tryconvert        | tryconvert121 |         trycast         |
+--------------------+-------------------------+-------------------------+-------------------------+---------------+-------------------------+
| 08/03/2017 3:51 PM | 2017-08-03 15:51:00.000 | 2017-03-08 15:51:00.000 | 2017-03-08 15:51:00.000 | NULL          | 2017-03-08 15:51:00.000 |
| 28/03/2017 7:30 AM | NULL                    | 2017-03-28 07:30:00.000 | 2017-03-28 07:30:00.000 | NULL          | 2017-03-28 07:30:00.000 |
+--------------------+-------------------------+-------------------------+-------------------------+---------------+-------------------------+

---

To select values that haven't been converted yet, you can use:

... where dt not like '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%'