Converting Integer Values to Datetime

Question

Post reply

Converting Integer Values to Datetime

chandan_jha18

SSChampion

Points: 11330
More actions
July 15, 2014 at 1:50 am

#290899

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

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

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Something like this?

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

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

My preferred method would be

😎

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

chandan_jha18 SSChampion Points: 11330 More actions · Answer 3

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

ScottPletcher SSC Guru Points: 101103 More actions · Answer 4

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".