Solved

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

Posted on 2008-10-01
6
604 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 250 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

691 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