Effective conversion of strings with date "31. 12. 2019" and "7/2020" to the date type?

Question

I am importing CSV files that contain dates in two alternative forms like:

31. 12. 2019
7/2020

The two forms may alternatively occur in the same column; so, the part of the solution have to be detection of the form.
As the second form 7/2020 does not contain the day information, I am using 1 for the day.
So far, I have worked only with the subset of records that used predictable form of the used date. I have followed the T-SQL documentation for the CONVERT() function (https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql). Let's assume @date = '31. 12. 2019' then the following conversion is done:
    CONVERT(date, @date, 104) AS my_date         -- the German form

For the @date = '7/2020' I do the following:
    CONVERT(date, '1/' + @date, 103) AS my_date  -- the British/French form

Now, if I do not know (in advance) what form the @date contains, how to convert it?
Actually, the date is not in a @date variable. Instead, it is the field value in the SELECT query -- say xdate. I expect the code to look like:
    CASE WHEN /* detect the British/French case */ THEN CONVERT(date, '1/' + xdate, 103)
         WHEN /* detect the German case */ THEN CONVERT(date, xdate, 104)
         ELSE NULL -- this does not happen, anyway...
    END AS my_date

... but there may be some better way.

allmhuran · Answer

I take it you're dumping the CSV into a table using bulk insert or some similar process, and the column in that table storing the unprocessed date is a varchar, and then you're running a query against the table to process the contents, yeah?
If that's the case, and there are only ever those two forms, you could use like, or charindex(). The performance is going to be pretty much the same as far as I know, so do whichever seems easier to read. Personally I'd probably use charindex:
select case 
          when charindex('/', xdate) > 0 then convert(date, '1/' + xdate, 103)
          else convert(date, xdate, 104)
       end

Or using like:
select case 
          when xdate like '%/%' then convert(date, '1/' + xdate, 103)
          else convert(date, xdate, 104)
       end

If those aren't the only two forms then you probably want to try_convert instead, or just cover all of your bases

Zohar Peled · Answer

A simple charindex can get you an indication of the string contains a slash (/) or not - which is a good enough indication if the text format is m/yyyy or dd. mm. yyyy.
However, you should use Try_convert and not convert because a case expression isn't guaranteed to short-circuit, meaning all when clauses might be evaluated.
Here's how I would write it:
SELECT CASE WHEN CHARINDEX('/', xdate) > 0 THEN TRY_CONVERT(date, '1/' + xdate, 103)
            ELSE TRY_CONVERT(date, xdate, 104)
       END As my_date

Zhorov · Answer

If I understand the question correctly, a possible solution is a combination of COALESCE() and TRY_CONVERT():
Statement:
SELECT COALESCE(
   TRY_CONVERT(date, xdate, 104), 
   TRY_CONVERT(date, '1/' + xdate, 103)
) AS xdate
FROM (VALUES
   ('31. 12. 2019'),
   ('7/2000'),
   ('wrong date 12/12')
) v (xdate)

Result:
xdate
----------
2019-12-31
2000-07-01
null

Effective conversion of strings with date "31. 12. 2019" and "7/2020" to the date type?

3 Answers

Add your own answers!

Ask a Question