Combine DATETIME column with CHAR field to create DATETIME2 column

Question

I am trying to import data from a vendor's database and combine two columns into one for our database. They have two columns are CITATION_DATE which is a datetime data type and CITATION_TIME which is a char(8) data type.

I would like to combine these two columns into one column issueDate which is of datetime2(7) data type.

I tried using Aaron's logic found here but I have not been successful in getting the query to execute. My suspicion is that I need more characters in the CITATION_TIME to form a valid time stamp but I am not sure.

Is there a way to combine these two fields into a single column - that follows the datetime2 format?

My attempt was to try and clean up bad values like empty strings or non-numeric characters:

;WITH issueDate AS
( 
    SELECT 
        TRY_CAST(vt.CITATION_DATE AS DATE) AS CITATION_DATE ,
        CASE WHEN RTRIM ( LTRIM ( vt.CITATION_TIME )) = '' THEN '0000'
             WHEN RTRIM ( LTRIM ( vt.CITATION_TIME )) = '000' THEN '0000'
             WHEN TRY_CAST(vt.CITATION_TIME AS INT) IS NULL THEN '0000'
             ELSE vt.CITATION_TIME
        END AS CITATION_TIME
    FROM   
        Oklahoma_PVD_WildlifeLaw.dbo.VIOLATOR_TICKETS AS vt
    --ORDER BY CITATION_TIME;
)
SELECT 
    CONVERT(DATETIME, CONVERT(CHAR(8), id.CITATION_DATE, 112) + ' ' + 
       CONVERT(CHAR(8), id.CITATION_TIME, 108))
FROM   
    issueDate AS id;

But I am getting the following error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

It does look like I do not have enough characters for the TIME portion - as this return values in a DATETIME format, but I lose all of my hours and minutes this way:

;WITH issueDate AS 
(
    SELECT 
        TRY_CAST(vt.CITATION_DATE AS DATE) AS CITATION_DATE,
        CASE 
           WHEN RTRIM(LTRIM(vt.CITATION_TIME)) = '' THEN '00:00:00.0000000'
           WHEN RTRIM(LTRIM(vt.CITATION_TIME)) = '000' THEN '00:00:00.0000000'
           WHEN TRY_CAST(vt.CITATION_TIME AS INT) IS NULL THEN '00:00:00.0000000'
           --ELSE vt.CITATION_TIME
        END AS CITATION_TIME
    FROM   
        Oklahoma_PVD_WildlifeLaw.dbo.VIOLATOR_TICKETS AS vt
    --ORDER BY CITATION_TIME;
)
SELECT 
    CONVERT(DATETIME, CONVERT(CHAR(8), id.CITATION_DATE, 112) + ' ' +
        CONVERT(CHAR(8), id.CITATION_TIME, 108)) AS [DateTime]
FROM   
    issueDate AS id
WHERE
    CONVERT(DATETIME, CONVERT(CHAR(8), id.CITATION_DATE, 112) + ' ' +
        CONVERT(CHAR(8), id.CITATION_TIME, 108)) IS NOT NULL;

A couple of questions. Does `CITATION_TIME` of 1830 represent `06:30:00 PM`, or is that a count of minutes or seconds or some such? Also, what are the `MIN` and `MAX` values of `CITATION_TIME`? — Eric Brandt, Dec 11 '18 at 18:38
That's a good question - I had assumed it was military time but I see a max value of 9799 - so that is going to require more questions from the customer. — MISNole, Dec 11 '18 at 18:48
Adventures in data cleaning. Keep us posted on what you find out! — Eric Brandt, Dec 11 '18 at 18:52
A maximum value of `9799` makes me wonder if it might be a fixed-point fraction of a day: `0.9799 day = 84663 seconds`. — HABO, Dec 11 '18 at 19:35

Andrey Nikolov · Answer 1 · 2018-12-11T18:49:45.037

Did you tried to calculate number of minutes from the time part and add them to the date using DATEADD?

select DATEADD(minute, cast(CITATION_TIME as int) % 100 + 60 * (cast(CITATION_TIME as int) / 100), CITATION_DATE)
FROM   Oklahoma_PVD_WildlifeLaw.dbo.VIOLATOR_TICKETS AS vt

This code assumes all dates has zero time part (midnight) and CITATION_TIME are all digits. The code above tries to handle these by casting the datetime to date (to get rid of the time part) and using try_cast to handle non-numeric times. If you need, you can use the same code:

select DATEADD(minute, t.IntTime % 100 + 60 * (t.IntTime / 100), cast(CITATION_DATE as date))
FROM   Oklahoma_PVD_WildlifeLaw.dbo.VIOLATOR_TICKETS AS vt
cross apply (select isnull(try_cast(CITATION_TIME as int), 0) as IntTime ) t

If there is invalid "times" in your data, you must decide how to handle them. Try to find these records by searching for values, where last 2 digits are >= 60 for example:

select * from Oklahoma_PVD_WildlifeLaw.dbo.VIOLATOR_TICKETS
where try_cast(right(CITATION_TIME, 2) as int) >= 60
   or (len(CITATION_TIME) = 4 and try_cast(left(CITATION_TIME, 2) as int) >= 24)       
   or len(CITATION_TIME) > 4

Thanks - there are invalid times in the Citation column - I see many values where the first two digits are greater than 24. — MISNole, Dec 11 '18 at 18:50

Combine DATETIME column with CHAR field to create DATETIME2 column

1 Answers1