Solved

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

Posted on 2008-10-01
6
598 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 59

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 59

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
Independent Software Vendors: 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

726 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