Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting Integer Values to Datetime Expand / Collapse
Author
Message
Posted Tuesday, July 15, 2014 1:50 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 466, Visits: 1,951
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
Post #1592436
Posted Tuesday, July 15, 2014 1:56 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, May 8, 2015 1:48 AM
Points: 5,224, Visits: 5,136
Something like this?

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

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





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1592437
Posted Tuesday, July 15, 2014 2:04 AM This worked for the OP Answer marked as solution
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:02 PM
Points: 3,857, Visits: 9,955
My preferred method would be

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

Post #1592440
Posted Tuesday, July 15, 2014 3:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 466, Visits: 1,951
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
Post #1592476
Posted Tuesday, July 15, 2014 1:48 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 2,807, Visits: 4,229
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1592783
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse