-3

I have been benefiting from TRY_CONVERT function thanks to a reply from my last question.

I am now facing a value in a key/value table (the value column of type NVARCHAR(50)) that cannot be converted to DATE even though it very much looks like a date.

enter image description here

I'd like to why TRY_CONVERT(DATE, [Claim issue complete]) should return a NULL.

Even though I use a substring of the value like this the result is the same

SELECT SUBSTRING([Claim issue complete], 1, 10) AS CompletionDate

UPDATE: A textual copy of the picture

Claim issue complete
2018-09-21 00:00:00+02
2017-10-12 00:00:00+02
2018-01-30 00:00:00+01
2017-07-19 00:00:00+02
2019-02-14 00:00:00+01
NULL
2017-08-06 00:00:00+02
2017-10-04 00:00:00+02
2018-05-18 00:00:00+02
2018-09-05 00:00:00+02
2019-01-14 00:00:00+01
2019-01-24 00:00:00+01
2018-03-21 00:00:00+01
2017-11-28 00:00:00+01
2018-02-26 00:00:00+01
2018-04-05 00:00:00+02
2018-04-27 00:00:00+02

Update 2: An Example

Here is a sample of the problem.

SELECT TRY_CONVERT(DATE, '2018-09-21 00:00:00+02')

The above returns NULL.

disasterkid
  • 6,948
  • 25
  • 94
  • 179
  • Is it a specific value where the conversion fails, or all/some of them? Can you create a [mcve] with DDL+DML for sample data so that we could reproduce the problem? – Zohar Peled Mar 17 '19 at 12:58
  • @ZoharPeled the values in the picture all return a `NULL` for `TRY_CONVERT(DATE, [Claim issue complete])`. – disasterkid Mar 17 '19 at 13:02
  • Well, I can't copy the values from the picture into a test environment, so I really can't see what the problem actually is. – Zohar Peled Mar 17 '19 at 13:03
  • @ZoharPeled sure. Sorry! I will paste the values in the question. I just hope no hidden characters are involved. – disasterkid Mar 17 '19 at 13:05
  • @ZoharPeled question updated with text for the dates. – disasterkid Mar 17 '19 at 13:08
  • Are you saying that the substring also returns null? – Salman A Mar 17 '19 at 13:10
  • 2
    *even though it very much looks like a date*!! Post the problematic value. – forpas Mar 17 '19 at 13:15
  • 1
    Just because something "looks" like a date, doesn't mean SQL Server will always convert it. For example `TRY_CONVERT(datetime, '2019-03-17')` and `TRY_CONVERT(date,'03/17/2019')` would both return `NULL` on my instance. There only 2 formats that guarentee the **correct** conversion regardless of data type and language; and they are `yyyyMMdd` and `yyyy-MM-dd-Thh:mm:ss.sssssss`. You haven't actually the value that is returning `NULL` here; so all we can tell you is that the date string isn't valid. – Thom A Mar 17 '19 at 13:16
  • Can you post the value for which the query returns NULL? Most probably it is invalid for this type of operation. – Ana Iuliana Tuhasu Mar 17 '19 at 13:29
  • @AnaIulianaTuhasu yes, please see the new update with a sample value. – disasterkid Mar 17 '19 at 13:41
  • @forpas please look at the Update 2 i the question. – disasterkid Mar 17 '19 at 13:42
  • @Larnu please look at the Update 2 i the question. – disasterkid Mar 17 '19 at 13:42
  • 1
    But you were using `SUBSTRING`, which does work. [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=45ae6848e74383ef1cdfdeacbb4250cf) – Thom A Mar 17 '19 at 13:57
  • @Larnu you are right. I just need to have a separate case for when the `[Claim issue complete]` is `NULL`. Because that would make `SUBSTRING` to return an error. You can write this as an answer. – disasterkid Mar 17 '19 at 14:38
  • `SUBSTRING` won't return an error is the value of the `varchar` is `NULL`. Are we missing a point of information here? – Thom A Mar 17 '19 at 15:09

3 Answers3

1

Ideally, you should change the database structure and store your values in the appropriate data type - if you need a date value store it as Date, if you need it with time store it as DateTime2 (Why not DateTime?) and if you need it to be time zone aware store it as DateTimeOffset.
For more information, read Aaron Bertrand's Bad habits to kick : choosing the wrong data type.

The string representation format you are using in your database does not fit any of the built in date time styles in SQL Server - that's why the Try_Convert fails (as will Try_Cast, for that matter).
If you are only interested in the date part of the value, you can use try_cast on the 10 left-most chars - since you are casting to Date and not DateTime the yyyy-mm-dd format is unambiguous and will always be converted correctly:

SELECT  TRY_CAST(LEFT('2018-09-21 00:00:00+02', 10) As DATE)

If you want to use Try_convert instead, you need to specify the style parameter - 120 in this case:

SELECT  TRY_CONVERT(DATE, LEFT('2018-09-21 00:00:00+02', 10), 120)

Please note that you don't need any code to handle null inputs - they will just come out as null

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
0

The reason that SELECT TRY_CONVERT(DATE, '2018-09-21 00:00:00+02') is'nt working is cause it is'nt a valid date format. To be specific, it is the +02 part in the end.
You could re-write it like this if you know the first 19 positions always will be YYYY-MM-DD hh:mm:ss:

SELECT TRY_CONVERT(DATE, LEFT('2018-09-21 00:00:00+02', 19))

I've just added the LEFT-function to sort out the +02 in the end

wenzzzel
  • 643
  • 2
  • 6
  • 17
0

Good day,

SELECT TRY_CONVERT(DATETIME2 , '2018-09-21 00:00:00 +02', 20) -- null, since this '+02' is not a legit OFFSET
SELECT TRY_CONVERT(DATETIME2 , '2018-09-21 00:00:00 +02:00', 20) -- OK
--Solution for bad OFFSET style can be to add the missing string:
SELECT TRY_CONVERT(DATETIME2 , '2018-09-21 00:00:00 +02' + ':00', 20) -- OK

--Same using Date:
SELECT TRY_CONVERT(DATE , '2018-09-21 00:00:00 +02' + ':00', 20) -- OK
Ronen Ariely
  • 2,336
  • 12
  • 21