Converting Integer Values to Datetime

  • Hi,

    As a DBA, I am working on a project where an ETL process(SSIS) takes a long time to aggregate and process the raw data.

    I figured out few things where the package selects the data from my biggest 200 GB unpartitioned table which has a datekey column but the package converts its each row to an integer value leading to massive scans and high CPU.

    Example: the package passed two values 20140714 and 4 which means it wants to grab data from my biggest table which belongs between 20140714 04:00:00 and 20140714 05:00:00.

    It leads to massive implicit conversions and I am trying to change this.

    To minimize the number of changes, what I am trying to do is to convert 20140714 and 4 to a datetime format variable.

    Select Convert(DATETIME, LEFT(20170714, 8)) which gives me a date value but I am stuck at appending time(HH:00:00) to it.

    Kindly suggest and give your inputs.

    Thanks

    Chandan Jha

  • Something like this?

    declare @date int = 20140714, @hour int = 4

    SELECT DATEADD(HOUR,@hour,CONVERT(DATETIME,LEFT(@date,8)))

  • My preferred method would be

    😎

    SELECT DATEADD(HOUR,@hour,CONVERT(DATETIME,CAST(@date AS VARCHAR(8)),112))

  • Thanks Anthony and Eirikur. Both solutions are great. I will see how to use them in the package now so as to avoid conversions for such a massive 200 GB table.

    Cheers!!

    Chandan Jha

  • You don't need to explicitly convert it. Just get the format to a string of YYYYMMDD, which is always a valid date/datetime format:

    SELECT DATEADD(HOUR, @hour, CAST(@date AS varchar(8)))

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 5 posts - 1 through 4 (of 4 total)

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