Converting seconds into time 1

[JAPixley]

I am not a DBA. I am just a grunt who knows some SQL. I babysit a proprietary IVR system that pretty much does what it is supposed to do, and everyone was happy until...
The statistics database was built to track when a call hits various menus in call path script (events). The main parent record for each call is in a table called Call_Stats. Child records which log each event for a given call are kept in a table named Stat_Events. The Call_Stats table has column which shows the start date/time of the call and another column which shows the entire duration of the call; obviously, every event for a given call falls within this window of time. The Stat_Events table has a column named Event_Time which has the time for a specific event, but it is recorded as seconds since midnight. The person who built the database and accompanying reports was fine with merely doing counts of events in a given time frame, based on the start time of the call, as was everyone who has ever received one of the automated reports from this database. Now there is a guy who started getting reports and he has somehow figured out that none of the existing reports give the exact time of a specific event, and he's making life difficult. I tried to write an ad hoc query for him so he'd go away, but I can't figure out how to automatically convert 'seconds since midnight' into an actual time and if there are more than a handful of records, it quickly becomes too much data to manually adjust. I've provided a few examples of what I've tried and the results I've gotten so far. And in case it matters, the Event_Time column properties show that it is part of a five-column primary key, the data type is numeric(18,0), and it does not allow nulls. Any help will be greatly appreciated.


SELECT TOP 3 [Event_Time] FROM [Stat_Events]
Event_Time
---------------------------------------
14709
14918
14870

SELECT TOP 3 ([Event_Time]/60/60) + ':' + ([Event_Time]/60%60) + ':' + ([Event_Time]%60) FROM [Stat_Events]
Error converting data type varchar to numeric.

SELECT TOP 3 ([Event_Time]/60/60) AS [Hour], ([Event_Time]/60%60) AS [Min], ([Event_Time]%60) AS [Sec] FROM [Stat_Events]
Hour Min Sec
------------- --------- ------
4.085833333 5.150000 9
4.143888883 8.633333 38
4.130555550 7.833333 50

SELECT TOP 3 CAST([Event_Time] AS timestamp) AS [TimeStamp] FROM [Stat_Events]
TimeStamp
------------------
0x1200000175390000
0x12000001463A0000
0x12000001163A0000

SELECT TOP 3 CAST([Event_Time] as datetime) AS [DateTime] FROM [Stat_Events]
DateTime
-----------------------
1940-04-10 00:00:00.000
1940-11-05 00:00:00.000
1940-09-18 00:00:00.000

SELECT TOP 3 CAST([Event_Time] as smalldatetime) AS [SmallDateTime] FROM [Stat_Events]
SmallDateTime
-----------------------
1940-04-10 00:00:00
1940-11-05 00:00:00
1940-09-18 00:00:00

What I would dearly love to be able to do, without do anything complicated that a non-DBA grunt would get yelled at for doing, is to get a result which looks like this:
Event_Time
-----------
04:05:09
04:08:38
04:07:50

Thanks in advance,
Alex

 

[RiverGuy]

SELECT DATEADD(second, YourColumnWithSecondsSinceMidnight, YourColumnThatStoresTheActualDate)

 

[gmmastros]

You could use DateAdd to add the seconds. When you do this without a date component, it will be added to Jan 1, 1900. But, since you only want to show the time, you could convert back to a string.

Select Top 3 Convert(VarChar(20), DateAdd(Second, Event_Time, 0), 108)
From   Stat_Events

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JAPixley]

RiverGuy, gmmastros,

Thanks so much for the swift replies. This was exactly what I needed.

Alex