[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2008-10-01
6
Medium Priority
?
612 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 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

656 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