Painful datetime conversion

  • I have a very awful table structure that I have to deal with. This is in an ERP and I can't change the tables...yes I know how completely horrible this is. This system stores dates and times in separate columns. This in itself isn't so bad but they are always stored as numerics. This causes some very ugly code to turn this horrible design into a datetime value that is actually usable. I have come up with a couple of ways of turning into a datetime but I am looking for other ideas in case somebody comes up with a better idea.

    Here is my table and data. The first two columns are the Date and Time values stored as numerics. The last column is the datetime I need this to be.

    CREATE TABLE #Something

    (

    VHRGDT numeric(8, 0) NOT NULL,

    VHRGTM numeric(6, 0) NOT NULL,

    DesiredDateTimeValue datetime

    )

    insert #Something

    select 20150817, 134818, '2015-08-17 13:48:18.000' union all

    select 20150818, 71406, '2015-08-18 07:14:06.000' union all

    select 20150818, 141636, '2015-08-18 14:16:36.000' union all

    select 20150819, 74938, '2015-08-19 07:49:38.000'

    select *

    from #Something

    Everything I have come up with is a mess of cast and stuff basically turning this mess into a string that can ultimately be cast as a datetime. Maybe some fresh eyes on this will come up with something better.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Try the FORMAT function. It was added in SQL 2012.

    SELECT FORMAT(vhrgdt, '0000-00-00') + ' ' + FORMAT(vhrgtm, '00:00:00.000'), *

    FROM #Something s

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen (9/16/2015)


    Try the FORMAT function. It was added in SQL 2012.

    SELECT FORMAT(vhrgdt, '0000-00-00') + ' ' + FORMAT(vhrgtm, '00:00:00.000'), *

    FROM #Something s

    Drew

    Thanks Drew. I don't use FORMAT very often but that is certainly a mountain simpler than what I was doing. The code I had worked but was just awful because I basically did all that formatting manually. :w00t:

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Or this if you want to use a MS provided scalar function:

    select *, msdb.dbo.agent_datetime(VHRGDT,VHRGTM)

    from #Something

  • Lynn Pettis (9/16/2015)


    Or this if you want to use a MS provided scalar function:

    select *, msdb.dbo.agent_datetime(VHRGDT,VHRGTM)

    from #Something

    Coincidentally just been looking at something that used that function and I went to take a look at it

    msdb.dbo.sp_helptext agent_datetime

    and then wished that I hadn't! I suppose if it works that's good enough ... although my code barfed trying to use that function as it didn't have EXEC permission to MSDB 🙁 I imagine I could have solved that ...

    ... but somewhere on my travels I saw this which satisfies my perception that an arithmetic algorithm will perform better than a string one, but the formula is a bit Smoke & Mirrors to me

    CONVERT(datetime, RTRIM(VHRGDT))

    + (VHRGTM * 9 +VHRGTM % 10000 * 6 + VHRGTM % 100 * 10)

    / 216e4

    Given that the date will only ever be 8 digits (as an INT column), I assume that RTrim is being used just to cast INT to VARCHAR ... I don't know if there is a reason for using RTrim - perhaps it performs better than CONVERT(varchar(8), VHRGDT)?

  • Sean, I can't wait to have a go at this one in the morning. I'll attempt a 2008 version and see what kind of performance I can get out of it.

  • Sean Lange (9/16/2015)


    I have a very awful table structure that I have to deal with. This is in an ERP and I can't change the tables...yes I know how completely horrible this is. This system stores dates and times in separate columns. This in itself isn't so bad but they are always stored as numerics. This causes some very ugly code to turn this horrible design into a datetime value that is actually usable. I have come up with a couple of ways of turning into a datetime but I am looking for other ideas in case somebody comes up with a better idea.

    Here is my table and data. The first two columns are the Date and Time values stored as numerics....

    Quick suggestion, create an iTVF for the conversion using a mix of convert and format string convertion, performs quite nicely

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    IF OBJECT_ID('dbo.TVFN_INT_DATE_TIME_TO_DATETIME') IS NOT NULL DROP FUNCTION dbo.TVFN_INT_DATE_TIME_TO_DATETIME;

    IF OBJECT_ID('tempdb..#Something') IS NOT NULL DROP TABLE tempdb..#Something;

    CREATE TABLE #Something

    (

    VHRGDT numeric(8, 0) NOT NULL,

    VHRGTM numeric(6, 0) NOT NULL,

    DesiredDateTimeValue datetime

    )

    insert #Something

    select 20150817, 134818, '2015-08-17 13:48:18.000' union all

    select 20150818, 71406, '2015-08-18 07:14:06.000' union all

    select 20150818, 141636, '2015-08-18 14:16:36.000' union all

    select 20150819, 74938, '2015-08-19 07:49:38.000'

    GO

    CREATE FUNCTION dbo.TVFN_INT_DATE_TIME_TO_DATETIME

    (

    @DATEINT INT

    ,@TIMEINT INT

    )

    RETURNS TABLE

    WITH SCHEMABINDING

    AS

    RETURN

    /* Replaced FORMAT(@TIMEINT,' 00:00:00.000') with Stuff and convert after Jeff Moden pointed

    out the poor performance of the FORMAT function

    */

    SELECT CONVERT(DATETIME,CONVERT(VARCHAR(8),@DATEINT,0) + CHAR(32)

    + STUFF(STUFF(STUFF(CONVERT(VARCHAR(8)

    ,10000000 + @TIMEINT,0),5,0,CHAR(58)),8,0,CHAR(58)),1,2,''),112) AS DTVALUE

    GO

    SELECT

    SG.VHRGDT

    ,SG.VHRGTM

    ,SG.DesiredDateTimeValue

    ,DTOUT.DTVALUE

    FROM #Something SG

    CROSS APPLY dbo.TVFN_INT_DATE_TIME_TO_DATETIME(SG.VHRGDT,SG.VHRGTM) AS DTOUT;

    Output

    VHRGDT VHRGTM DesiredDateTimeValue DTVALUE

    --------- -------- ----------------------- -----------------------

    20150817 134818 2015-08-17 13:48:18.000 2015-08-17 13:48:18.000

    20150818 71406 2015-08-18 07:14:06.000 2015-08-18 07:14:06.000

    20150818 141636 2015-08-18 14:16:36.000 2015-08-18 14:16:36.000

    20150819 74938 2015-08-19 07:49:38.000 2015-08-19 07:49:38.000

  • Sean Lange (9/16/2015)


    drew.allen (9/16/2015)


    Try the FORMAT function. It was added in SQL 2012.

    SELECT FORMAT(vhrgdt, '0000-00-00') + ' ' + FORMAT(vhrgtm, '00:00:00.000'), *

    FROM #Something s

    Drew

    Thanks Drew. I don't use FORMAT very often but that is certainly a mountain simpler than what I was doing. The code I had worked but was just awful because I basically did all that formatting manually. :w00t:

    DO NOT USE FORMAT!!!! PERIOD!!!! It takes 44 times longer than either CAST or CONVERT. I suspect that it's a piece of really bad Regex behind the scenes.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden (9/16/2015)


    Sean Lange (9/16/2015)


    drew.allen (9/16/2015)


    Try the FORMAT function. It was added in SQL 2012.

    SELECT FORMAT(vhrgdt, '0000-00-00') + ' ' + FORMAT(vhrgtm, '00:00:00.000'), *

    FROM #Something s

    Drew

    Thanks Drew. I don't use FORMAT very often but that is certainly a mountain simpler than what I was doing. The code I had worked but was just awful because I basically did all that formatting manually. :w00t:

    DO NOT USE FORMAT!!!! PERIOD!!!! It takes 44 times longer than either CAST or CONVERT. I suspect that it's a piece of really bad Regex behind the scenes.

    Thanks so much for the heads up on the FORMAT Jeff, I had totally forgotten about that one and BTW the difference is closer to 100 times.

    😎

    Just in case anyone is considering using that format hog, have a look at this:

    USE tempdb;

    GO

    SET NOCOUNT ON

    /* Format function vs. string conversion */

    IF OBJECT_ID('dbo.TBL_SAMPLE_TIME_INT') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_TIME_INT;

    CREATE TABLE dbo.TBL_SAMPLE_TIME_INT

    (

    STI_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_SAMPLE_TIME_INT_STI_ID PRIMARY KEY CLUSTERED

    ,STI_TIME_INT INT NOT NULL

    );

    DECLARE @TTIME TIME = CONVERT(TIME,'00:00:00',0);

    INSERT INTO dbo.TBL_SAMPLE_TIME_INT(STI_TIME_INT)

    SELECT TOP(1000000)

    CONVERT(INT,REPLACE(CONVERT(VARCHAR(8),DATEADD(SECOND,CHECKSUM(NEWID()) % 864000,@TTIME),121),CHAR(58),''),0)

    FROM sys.all_columns SAC1

    CROSS JOIN sys.all_columns SAC2

    CROSS JOIN sys.all_columns SAC3

    ;

    DECLARE @CHAR_BUCKET VARCHAR(8) = '';

    RAISERROR('---FORMAT 1 ---',0,0) WITH NOWAIT;

    SET STATISTICS TIME ON;

    SELECT

    @CHAR_BUCKET = FORMAT(STI.STI_TIME_INT,' 00:00:00.000')

    FROM dbo.TBL_SAMPLE_TIME_INT STI;

    SET STATISTICS TIME OFF;

    RAISERROR('---CONVERT 1 ---',0,0) WITH NOWAIT;

    SET STATISTICS TIME ON;

    SELECT

    @CHAR_BUCKET = STUFF(STUFF(STUFF(CONVERT(VARCHAR(8),10000000 + STI.STI_TIME_INT,0),5,0,CHAR(58)),8,0,CHAR(58)),1,2,'')

    FROM dbo.TBL_SAMPLE_TIME_INT STI;

    SET STATISTICS TIME OFF;

    RAISERROR('---FORMAT 2 ---',0,0) WITH NOWAIT;

    SET STATISTICS TIME ON;

    SELECT

    @CHAR_BUCKET = FORMAT(STI.STI_TIME_INT,' 00:00:00.000')

    FROM dbo.TBL_SAMPLE_TIME_INT STI;

    SET STATISTICS TIME OFF;

    RAISERROR('---CONVERT 2 ---',0,0) WITH NOWAIT;

    SET STATISTICS TIME ON;

    SELECT

    @CHAR_BUCKET = STUFF(STUFF(STUFF(CONVERT(VARCHAR(8),10000000 + STI.STI_TIME_INT,0),5,0,CHAR(58)),8,0,CHAR(58)),1,2,'')

    FROM dbo.TBL_SAMPLE_TIME_INT STI;

    SET STATISTICS TIME OFF;

    Output

    ---FORMAT 1 ---

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 18221 ms, elapsed time = 21281 ms.

    ---CONVERT 1 ---

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 281 ms, elapsed time = 274 ms.

    ---FORMAT 2 ---

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 18876 ms, elapsed time = 21356 ms.

    ---CONVERT 2 ---

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 265 ms, elapsed time = 274 ms.

  • Hi Sean, was your code as nasty looking as mine below?

    SELECT

    DATEADD(SECOND, CONVERT(INT, SUBSTRING(RIGHT('0' +(CONVERT(VARCHAR(6), VHRGTM)),6),1,2)) * 60 * 60 + CONVERT(INT, SUBSTRING(RIGHT('0' +(CONVERT(VARCHAR(6), VHRGTM)),6),3,2)) * 60 + SUBSTRING(RIGHT('0' +(CONVERT(VARCHAR(6), VHRGTM)),6),5,2), CONVERT(DATETIME, CONVERT(VARCHAR(8), VHRGDT),113)),

    DesiredDateTimeValue

    FROM #Something

  • Daytime here, so my servers are under load and I cannot get a consistent runtime, but this one looks to perform best for me.

    SELECTDateTimeFromParts(VHRGDT / 10000, (VHRGDT % 10000) / 100, VHRGDT % 100

    , VHRGTM / 10000, (VHRGTM / 100) % 100, VHRGTM % 100, 0)

    , DesiredDateTimeValue

    FROM#Something

  • Another option for you....

    SELECT CONVERT( DATETIME, CONVERT(VARCHAR(8), VHRGDT), 113) + CONVERT(DATETIME, STUFF(STUFF(RIGHT('0'+CONVERT(VARCHAR(6), VHRGTM), 6),3,0,':'),6,0,':'),108) , DesiredDateTimeValue FROM #Something

  • Or you could create a lookup table with all 86400 seconds in and then use a query like the one below....

    SELECT

    S.DesiredDateTimeValue

    , CONVERT(DATETIME, CONVERT(VARCHAR(8), S.VHRGDT)) + CONVERT(DATETIME, T.ATime) [DateTime]

    FROM #Something S

    JOIN Times T on T.VHRGTM = S.VHRGTM

    It only takes a couple of seconds to populate the lookup table.

    drop table Times;

    create table Times

    (

    ATime time not null

    , VHRGTM numeric(6, 0) NOT NULL primary key,

    )

    set nocount on

    declare @time time

    set @time = convert(datetime, 0);

    while @time < '23:59:59'

    begin

    insert into times(ATime, VHRGTM) select @time, convert(numeric(6,0), replace(convert(varchar(8), @time, 108), ':',''))

    set @time = dateadd(second, 1, @time)

    end

    insert into times(ATime, VHRGTM) select @time, convert(numeric(6,0), replace(convert(varchar(8), @time, 108), ':',''))

  • I understand not being able to modify the existing tables , but would it be possible the simple add 2 date tables with a set of dates and times , I am assuming seconds resolution isnt important here. And them simply join the columns as needed , I think this would help for lookups as well as scans be probably behave much better with filters.

    Jayanth Kurup[/url]

  • Here's the function I came up with. It first left-pads the integers with zeros out to the length they should be to accommodate the case where you have a year less than 1000 or an hour less than 10, resulting in an integer that's too short. It then parses and formats the strings and converts the output.

    IF OBJECT_ID('dbo.IntegersToDatetime', 'if') IS NOT NULL DROP FUNCTION dbo.IntegersToDatetime;

    go

    CREATE FUNCTION dbo.IntegersToDatetime(@intDate Integer,

    @intTime Integer) RETURNS TABLE WITH SCHEMABINDING

    AS

    RETURN (

    WITH cteConversions AS (

    SELECT normalized_date = RIGHT('00000000' + CONVERT(Varchar(8), NULLIF(@intDate, 0)), 8), --CONVERT(char(8), NULLIF(@intDate, 0)),

    normalized_time = RIGHT('000000' + CONVERT(Varchar(6), NULLIF(@intTime, 0)), 6)

    ),

    cteFormatted AS (

    SELECT ProperDate = SUBSTRING(normalized_date, 1, 4) + '/' + SUBSTRING(normalized_date, 5, 2) + '/' + SUBSTRING(normalized_date, 7, 2),

    ProperTime = SUBSTRING(normalized_time, 1, 2) + ':' + SUBSTRING(normalized_time, 3, 2) + ':' + SUBSTRING(normalized_time, 5, 2)

    FROM cteConversions

    )

    SELECT ProperDate, ProperTime, CONVERT(Datetime, ProperDate + ' ' + ProperTime) ProperDateTime

    FROM cteFormatted);

    go

    To test it, I created a table with column names that don't remind me of days past. Sean, I think you know what I'm talking about. 😉

    IF OBJECT_ID('dbo.Something') IS NOT NULL DROP TABLE dbo.Something;

    CREATE TABLE dbo.Something (

    id integer identity(1, 1) NOT NULL,

    CONSTRAINT Something_PK PRIMARY KEY CLUSTERED (id),

    integer_date integer not null,

    integer_time integer not null);

    I then loaded it up with 1M rows using Eirikur's excellent formula for the time. The dbo.TallyN is an implementation of Itzik Ben-Gan's virtual tally table using cascading CTEs that results in zero reads. Sean, I'm sure you have one already, but if anyone else needs it, it's published at http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers.

    DECLARE @TTIME TIME = CONVERT(TIME,'00:00:00', 0);

    INSERT INTO dbo.Something(integer_date, integer_time)

    SELECT CONVERT(Integer, Convert(Varchar(10), DATEADD(day, d.N, '01/01/1900'), 112)),

    CONVERT(Integer, REPLACE(CONVERT(Varchar(8), DATEADD(second, CHECKSUM(NEWID()) % 864000, @TTIME), 121), CHAR(58), ''),0)

    FROM dbo.TallyN(1000000) d;

    Overall, the performance wasn't terrible:

    SQL Server Execution Times:

    CPU time = 1779 ms, elapsed time = 3987 ms.

    There was a little variance in the times, but not much. I know the function isn't the prettiest in the world, but it's inside an ITVF, so you won't have to look at it every time. Will this work for you?

Viewing 15 posts - 1 through 15 (of 63 total)

You must be logged in to reply to this topic. Login to reply