?
Solved

query won't show time in seconds, field does have seconds in it

Posted on 2008-10-01
6
Medium Priority
?
616 Views
Last Modified: 2008-10-03
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,CallTypeId,CallCategoryId,DialedNum,Station,NearOffHookDt,FarOffHookDt,NearOnHookDt,FarOnHookDt,BeginGaurdDt,EndGaurdDt,CallInsertDt,ANI,DNIS,Site_Id,Switch_Id,ResourceGroup_Id,ResourceGroupType,Circuit_Id,ChannelId,InfoDigits,AgentSiteId,AgentSwitchId,DetailTable,CallerId,
-----------,-----------,-----------,-----------,-------,-----------,--------------,---------,-------,-------------,------------,------------,-----------,------------,----------,------------,---,----,-----------,-----------,----------------,-----------------,-----------,-----------,----------,-----------,-------------,-----------,--------,
Mar 25 2008 12:03PM,39792318,1856,236,NULL,8,1,NULL,NULL,Mar 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,3033913249,
Mar 25 2008 12:04PM,39792373,1874,242,NULL,8,1,NULL,NULL,Mar 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,15,NULL,NULL,NULL,CD,6098718949,
Mar 25 2008 12:04PM,39792315,1864,242,NULL,8,1,NULL,NULL,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,Mar 25 2008 12:04PM,4134554636,0000,1,1,0,4,18,11,NULL,NULL,NULL,CD,4134554636,
Mar 25 2008 12:04PM,39792323,1866,242,NULL,8,1,NULL,NULL,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,Mar 25 2008 12:04PM,6034875059,0000,1,1,0,4,24,4,NULL,NULL,NULL,CD,6034875059,
Mar 25 2008 12:04PM,39792319,1861,236,NULL,8,1,NULL,NULL,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,Mar 25 2008 12:04PM,9783725552,0000,1,1,0,4,18,23,NULL,NULL,NULL,CD,9783725552,
Mar 25 2008 12:04PM,39792331,1859,236,NULL,8,1,NULL,NULL,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,Mar 25 2008 12:04PM,2074579145,0000,1,1,0,4,18,20,NULL,NULL,NULL,CD,2074579145,
Mar 25 2008 12:04PM,39792321,1867,242,NULL,8,1,NULL,NULL,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,Mar 25 2008 12:04PM,6034372977,0000,1,1,0,4,24,2,NULL,NULL,NULL,CD,6034372977,
Mar 25 2008 12:03PM,39792317,1857,236,NULL,8,1,NULL,NULL,Mar 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.
0
Comment
Question by:VSSupport
6 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22615765
What is the data type of the field?  You can try the date formats:
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.
0
 
LVL 5

Accepted Solution

by:
libin_v earned 750 total points
ID: 22615807
The default display format for dates is Apr 15 1987 10:23PM. (Refer http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks36.htm )

Use

select convert(char(50),CallStartDt, 9) from ....
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22616284
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.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 6

Expert Comment

by:IncisiveOne
ID: 22616381
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().
0
 

Author Comment

by:VSSupport
ID: 22616430
select convert(char(50),CallStartDt, 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
0
 
LVL 19

Assisted Solution

by:grant300
grant300 earned 750 total points
ID: 22616496
You will either have to use an explicit select list which names all of the columns you want plus the convert(....) on the CallStartDt to get what you want, OR, you can do

select *, convert(varchar(32),CallStartDt,9) as PreciseCallStartDt from calldetail............

That will give you everything you had before plus the date information you want in a field called PreciseCallStartDt.

Regards,
Bill
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question