Conversion from seconds to readable date 2

[frankrizzojerky]

Cisco Call Manager logs call data to sql and stores the dates in the format "seconds since midnight jan 1 1970.

My question is, how would I go about converting this to readable format either using a stored precedure or an sql query in access. I was able to sort of do something in access, but it doesnt work so well.

Here is the sql query that doesnt work so well:
SELECT dbo_CallDetailRecord.callingPartyNumber AS Caller, dbo_CallDetailRecord.finalCalledPartyNumber AS Called, dbo_CallDetailRecord.duration AS Duration, CVDate(Format(((dbo_dateTimeOrigination/86400)+DateDiff("d",#1/1/1900#,#1/1/1970#)),"dd mm yyyy hh:nn:ss&quot) AS dateTimeOrigination_dt
FROM dbo_CallDetailRecord
WHERE (((dbo_CallDetailRecord.callingPartyNumber)="1220&quot);


It doesnt really spit out the correct date conversions as far as I can tell, and it assigns the same date (I am assuming the date of the first call in the log) to all of the rows.

Any ideas?

 

[frankrizzojerky]

My bad. The regular search function didnt turn up anything, but when I went to advanced after I posted I found some posts. I will try those before I post back

 

[frankrizzojerky]

Okay, so i did some fiddling and I still need some help.

I have changed my sql query to this:

SELECT dbo_CallDetailRecord.callingPartyNumber AS Caller, dbo_CallDetailRecord.finalCalledPartyNumber AS Called, dbo_CallDetailRecord.duration AS Duration, DateDiff([ss],'1970-01-01',getdate([dateTimeOrigination])) AS Expr1
FROM dbo_CallDetailRecord
WHERE (((dbo_CallDetailRecord.callingPartyNumber)="1220&quot);


I get a "Undefined function 'getdate' in expression"

The dateTimeOrigination is in dbo_CallDetailRecord

 

[SQLBill]

Proper function syntax is:

getdate()

-SQLBill

 

[MaffewW]

SQLBill, unless I'm mistaken frankrizzojerky isnt after the getdate() function, I think he was using it as a reference to a column

does this do the trick for starters

SELECT dbo_CallDetailRecord.callingPartyNumber AS Caller, dbo_CallDetailRecord.finalCalledPartyNumber AS Called, dbo_CallDetailRecord.duration AS Duration, DateDiff([ss],'1970-01-01',([dateTimeOrigination])) AS Expr1
FROM dbo_CallDetailRecord
WHERE (((dbo_CallDetailRecord.callingPartyNumber)='1220'))


Matt

Brighton, UK

 

[frankrizzojerky]

It doesnt error out when I try to save it but when I change to datasheet view it says #error in the column

Matt, you are correct in that I am looking to have this done to a column of results. I want a qeury to be able to pull up call details from a user's extension or calls incomming in for various reasons. THe problem I am running into is the date time getting displayed as 1054324874 or something like that, which makes getting anything useful out of the results impossible.

 

[SQLSister]

I think you are going to have to manually convert the data to date time as it isn't really a datetime field (at least as i understand what you said)

If you are using SQL server 2000, I would write a User-defined function to do this as it surely won't be the only time you need to do the conversion.

Now we know the begin data and the number of seconds since that date. If we divide by the number of seconds in a day and truncate the result to drop the decimal places (see round function for how to do this), we know the number of days since the begin date and thus can use dateadd function to calculate the actual day.

Use the modulo to get the remainder of seconds and use this to calculate the hours by dividing by the number of seonds in an hour. Then do the same for minutes and the final remainder is the seconds.

Once the function is done, then just call it in the select statement with the column name as the parameter.

 

[frankrizzojerky]

I am running sql 2000 where I would be storing any backend stuff, but the tables are in sql 7 on the call manager server. Does this qualify as using sql 2000 as far as a user defined function and module go?

 

[sunila7]

Hi,

Try this query ...

is this wht u r looking for

SELECT dbo_CallDetailRecord.callingPartyNumber Caller,
dbo_CallDetailRecord.finalCalledPartyNumber Called, dbo_CallDetailRecord.duration Duration,
dateadd(ss,dbo_dateTimeOrigination,'01/01/1970') dateTimeOrigination_dt
FROM dbo_CallDetailRecord
WHERE dbo_CallDetailRecord.callingPartyNumber='1220'

Sunil

 

[rathna01]

Hi
Try this out for Eastern Time in US.
SELECT
a.callingPartyNumber,
a.finalCalledPartyNumber,
a.originalCalledPartyNumber,
dateadd(second,ISNULL((a.dateTimeOrigination-18000),a.dateTimeOrigination),'19700101 00:00:00') as Originated,
dateadd(second,ISNULL((a.dateTimeConnect-18000),a.dateTimeConnect),'19700101 00:00:00') as Connected,
dateadd(second,ISNULL((a.dateTimeDisconnect-18000),a.dateTimeDisconnect),'19700101 00:00:00') as Disconnected,
FROM
dbo.CallDetailRecord a
WHERE
dateadd(second,a.dateTimeOrigination-18000,'19700101 00:00:00') between :StartDate and :EndDate

 

[NoCoolHandle]

<snip>an sql query in access.</snip>

Access doesn't do this as well as sqlserver.. Make sure you are using a passthrough query.

rathna01 has hit the money on the head about the query you need in SQL Server..

my 1c

Rob

 

[rathna01]

Rob,
I am using this query in Actuate to hit the SQL Server to get the report, Its working fine.

 

[frankrizzojerky]

I entered this (what rathna01 did) into query analyzer and it didnt work:

I get this error:
Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'FROM'.

I am directly querying against sql 7, could that be it?

 

[rathna01]

remove the , before from statement and try

 

[frankrizzojerky]

That worked. I was then getting this:

Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near ':'.


Which is this line:
dateadd(second,a.dateTimeOrigination-18000,'19700101 00:00:00') between :StartDate and :EndDate

So I took out the : next the startdate and enddate.

That passed without errors when I parse in the query analyzer, but when I try to execute it from there I get this:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'StartDate'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'EndDate'.



Id like to say that I am learning quite a few things about how to properly format queries and I really appreciate it. I am buying some books in the next couple of days, but I really appreciate you all helping me even though I am obviously new at this.

 

[rathna01]

Frank,

try this out, this should work, since :Startdate and :Enddate are input parameters I used to pass in Actuate report.

SELECT
a.callingPartyNumber,
a.finalCalledPartyNumber,
a.originalCalledPartyNumber,
dateadd(second,ISNULL((a.dateTimeOrigination-18000),a.dateTimeOrigination),'19700101 00:00:00') as Originated,
dateadd(second,ISNULL((a.dateTimeConnect-18000),a.dateTimeConnect),'19700101 00:00:00') as Connected,
dateadd(second,ISNULL((a.dateTimeDisconnect-18000),a.dateTimeDisconnect),'19700101 00:00:00') as Disconnected
FROM
dbo.CallDetailRecord a
WHERE a.callingPartyNumber ='1220'

check this and post it what happend. good luck

 

[frankrizzojerky]

That worked! Thanks so much. The only thing is that it is 1 hour off.( a value is reported as 11:38 where it should be 12:38 Which numbers should I edit to change this?

 

[MaffewW]

I think if you change the 18000's to 12000's that should do the trick

Matt

Brighton, UK

 

[rathna01]

Frank,

r u in Eastern Time Zone or Centeral or Mountain,I am in Eastern Time Zone -5:00 GMT (18000), I dont know how to check the daylight savings (+1 Hour or -1 Hours). I am trying to find out the way how to Implement this daylight saving.

 

[frankrizzojerky]

I am in eastern standard.

Matt, chaning it to 12000's makes it real slow and also makes it report the wrong times.