you're reading...
Data Warehouse, T-SQL

T-SQL INT to Time

I recently made the wise or not so wise decision to convert all the TimeID columns in a new data warehouse project from Int  to Time data types. This was done because the ETL was going to calculate the UTC dates and times based on these values, which is obviously easier to do if they are already date and time columns. Of course when I ran the first bulk load into staging it failed complaining the it was unable to convert a character data type into a date or time data type.  The Int to date conversion was straight forward enough:

CONVERT (datetime,convert(char(8),[intDateField] ))

But the Time conversion turned out to be a bit trickier as they are integers and there are no leading zeros so the incoming data could be 5 or 6 characters (assuming no milliseconds). After a little research and trial and error I came up with the following bit of code which seems to work consistently:

LEFT(CAST(LEFT(cast(RIGHT(‘000000’ + CAST([intTimeField] AS VARCHAR), 6) as varchar), 2) + ‘:’ + RIGHT(LEFT(cast(RIGHT(‘000000’ + CAST([intTimeField] AS VARCHAR), 6) as varchar), 4), 2) AS TIME),8) as [intTimeField]

I hope this saves you some time


Bonn Appetite!

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.


No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: