• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 627
  • Last Modified:

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,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
VSSupport
Asked:
VSSupport
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
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
 
libin_vCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
IncisiveOneCommented:
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
 
VSSupportAuthor Commented:
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
 
grant300Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now