VSSupport
asked on
query won't show time in seconds, field does have seconds in it
running the following command
select * from calldetail where CallStartDt > '09/01/2008' and Service_Id = 235 or service_id = 236 or service_id = 242
from sql advantage
and i get this output
CallStartDt,SeqNum,CallId, Service_Id ,User_Id,C allTypeId, CallCatego ryId,Diale dNum,Stati on,NearOff HookDt,Far OffHookDt, NearOnHook Dt,FarOnHo okDt,Begin GaurdDt,En dGaurdDt,C allInsertD t,ANI,DNIS ,Site_Id,S witch_Id,R esourceGro up_Id,Reso urceGroupT ype,Circui t_Id,Chann elId,InfoD igits,Agen tSiteId,Ag entSwitchI d,DetailTa ble,Caller Id,
-----------,-----------,-- ---------, ---------- -,-------, ---------- -,-------- ------,--- ------,--- ----,----- --------,- ---------- -,-------- ----,----- ------,--- ---------, ---------- ,--------- ---,---,-- --,------- ----,----- ------,--- ---------- ---,------ ---------- -,-------- ---,------ -----,---- ------,--- --------,- ---------- --,------- ----,----- ---,
Mar 25 2008 12:03PM,39792318,1856,236, NULL,8,1,N ULL,NULL,M ar 25 2008 12:03PM,Mar 25 2008 12:03PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,3033913249,0000,1, 1,0,4,5,3, NULL,NULL, NULL,CD,30 33913249,
Mar 25 2008 12:04PM,39792373,1874,242, NULL,8,1,N ULL,NULL,M ar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:05PM,Mar 25 2008 12:05PM,Mar 25 2008 12:05PM,Mar 25 2008 12:05PM,Mar 25 2008 12:05PM,6098718949,0000,1, 1,0,4,18,1 5,NULL,NUL L,NULL,CD, 6098718949 ,
Mar 25 2008 12:04PM,39792315,1864,242, NULL,8,1,N ULL,NULL,M ar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,4134554636,0000,1, 1,0,4,18,1 1,NULL,NUL L,NULL,CD, 4134554636 ,
Mar 25 2008 12:04PM,39792323,1866,242, NULL,8,1,N ULL,NULL,M ar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,6034875059,0000,1, 1,0,4,24,4 ,NULL,NULL ,NULL,CD,6 034875059,
Mar 25 2008 12:04PM,39792319,1861,236, NULL,8,1,N ULL,NULL,M ar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,9783725552,0000,1, 1,0,4,18,2 3,NULL,NUL L,NULL,CD, 9783725552 ,
Mar 25 2008 12:04PM,39792331,1859,236, NULL,8,1,N ULL,NULL,M ar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,2074579145,0000,1, 1,0,4,18,2 0,NULL,NUL L,NULL,CD, 2074579145 ,
Mar 25 2008 12:04PM,39792321,1867,242, NULL,8,1,N ULL,NULL,M ar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,Mar 25 2008 12:04PM,6034372977,0000,1, 1,0,4,24,2 ,NULL,NULL ,NULL,CD,6 034372977,
Mar 25 2008 12:03PM,39792317,1857,236, NULL,8,1,N ULL,NULL,M ar 25 2008 12:03PM,Mar 25 2008 12
problem is the db actually contains second information, however all the dates are rounded off at minutes.
i cannot find a way to run the query to show seconds.
select * from calldetail where CallStartDt > '09/01/2008' and Service_Id = 235 or service_id = 236 or service_id = 242
from sql advantage
and i get this output
CallStartDt,SeqNum,CallId,
-----------,-----------,--
Mar 25 2008 12:03PM,39792318,1856,236,
Mar 25 2008 12:04PM,39792373,1874,242,
Mar 25 2008 12:04PM,39792315,1864,242,
Mar 25 2008 12:04PM,39792323,1866,242,
Mar 25 2008 12:04PM,39792319,1861,236,
Mar 25 2008 12:04PM,39792331,1859,236,
Mar 25 2008 12:04PM,39792321,1867,242,
Mar 25 2008 12:03PM,39792317,1857,236,
problem is the db actually contains second information, however all the dates are rounded off at minutes.
i cannot find a way to run the query to show seconds.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Cool. I actually answered a Sybase question correctly. :)
The link I provided will show you all the formats including 9 and how they display on page for your reference.
The link I provided will show you all the formats including 9 and how they display on page for your reference.
Yeah, but ... how do you know the "db contains seconds info". look at the datatype in the db (not what you want to convert to for the display). datetime and time has seconds and milliseconds to a resolution of 3 milliseconds; smalldatetime has minutes only, no seconds or milliseconds. once you are sure it is datetime or time, then go right ahead and convert().
ASKER
select convert(char(50),CallStart Dt, 9) from calldetail where Service_Id = 235 or service_id = 236 or service_id = 242
shows the time, but gives me alot less information, infact now it only shows me the callstartdt field? it just want it to show the seconds
here is what i had to do
from MS SQL import from the SYBASE server to a temp db, once i imported my original query, within ms sql i was able to see the seconds and run the reported i needed
shows the time, but gives me alot less information, infact now it only shows me the callstartdt field? it just want it to show the seconds
here is what i had to do
from MS SQL import from the SYBASE server to a temp db, once i imported my original query, within ms sql i was able to see the seconds and run the reported i needed
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://www.comsci.us/sybase/date_fmt.html
Not as familiar with this in Sybase, but I know if it is a datetime field in MS SQL, the seconds are included. These are SQL examples -- maybe can help:
select Cast('Mar 25 2008 12:03:34PM' AS datetime)
select getdate()
Both of those return seconds with date and time for me, but again I am on MS SQL server. If it didn't work that way in SQL, I would use Convert with format which I have linked for you above the syntax from Sybase to do the same.