Working with time in T-SQL

Question

Working with time in T-SQL

Luke L

SSC-Dedicated

Points: 35547
More actions
September 26, 2005 at 2:59 pm

#95259

Hello, I've got a somewhat interesting issue that I'm in the process of working through and was wondering if anyone out there had seen anything like it before and might be able to point me in the right direction. I have an application that stores a timestamp as the number of seconds since midnight. I'm attempting to link to a table that stores a timestamp in noraml yyyy-mm-dd hh:mm:ss format. To do this I need to convert the number of seconds since mindnight to an actual time for example, 3600 would be 1:00:00.
Unfortunately I'm querying a non-SQL datasource so I'm somewhat lmited in what I can implement. I'll be using a query either in DTS or as an openquery on a linked server depending on what ends up working better.
Any help would be greatly appreciated.
To help us help you read this[/url]For better help with performance problems please read this[/url]

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

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

philcart SSC-Forever Points: 47796 More actions · Answer 1

Is the destination going to be SQL Server? If so, then your best bet would be to load the data into a staging table in the SQL Server database. Then you can use all the usual SQL functions.

--------------------
Colt 45 - the original point and click interface

Gift Peddie SSC Guru Points: 73570 More actions · Answer 2

The main problem is there is no time stamp data type in SQL Server, the time stamp is a derived data type used internally by SQL Server. To get seconds check .NET FCL (framework class library) 1.1 System.TimeSpan for the Duration method it will give you seconds. System.TimeSpan like all database types are under Structs in .NET because they are really not classes. Hope this helps.

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

This will convert the number of seconds since midnight to a datetime (for today) datatype so you can do the join...

SELECT DATEADD(ss,secondssincemidnightcol,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))

--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)

amackay_1 Ten Centuries Points: 1240 More actions · Answer 4

Alternatively you could convert the sql date/time column back to seconds from midnight and then join that to the non-sql column. You would use the datediff function to calculate the seconds.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

Thanks for your help guys. I was able to convert the seconds to a date/time with some dateadd functions, to my unfortunate surprise that when the application I'm pulling the data from doesn't write the same timestamp to both tables. They are a few seconds off from the processing it took to write each record. Looks like I'll have to figure out a different approach. Thanks again.

To help us help you read this[/url]For better help with performance problems please read this[/url]