Solved

HOW TO RETRIEVE THE TIME USING SQL STATEMENT

Posted on 2004-09-02
10
370 Views
Last Modified: 2010-04-05
hello there,
can i retrieve only the 'time' from the datetime?

exmaple:
A datetime like '2004-09-02 13:42:002', i want to take out only the time '13:42:002'

is it possible to do this?
and some more i want to find out the difference between two date/datetime.
It can be done using DATEDIFF in SQL but it only give me the result in either hour or minute or second or m. Second.

I want to result to be something like this :-
The Difference between this two date is 1 hour 30 minutes 39 second OR 1:30:39

thanks you
josh
0
Comment
Question by:joshfly
[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
10 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 11961932
1.TO_Char(DateTimeField,'HH:MI:SS');

2. Difference := DateTimeField1 - DateTimeField2;
whereas the frac part of the result is the time,
and the part before the decimal-delimiter are days

meikl ;-)
0
 
LVL 23

Expert Comment

by:Ferruccio Accalai
ID: 11961947
to get the time from a datetime you can use decodetime from sysutils

procedure TForm1.Button1Click(Sender: TObject);
var
 Year, Month, Day, Hour, Min, Sec, MSec: Word;
 begin
  DecodeTime(Now, Hour, Min, Sec, MSec);
end;
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 11961956
oops, sorry, i thought i was in the oracle topic

1. result := TimeToStr(DateField);

2. result := timetostr(DateField1-Datefield2);

meikl ;-)
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 23

Expert Comment

by:Ferruccio Accalai
ID: 11962008
mmm...
hi meikl, nice to see you :))

for 2 i'd do
 result := TimeToStr((SecondsBetween(datefield1,datefield2))/(60*60*24));
0
 
LVL 5

Expert Comment

by:Voodooman
ID: 11962617
Hi

The sql depends what database you are using. What db is it?


Voodooman
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 11962632
good point, voodoo,

overlooked "USING SQL STATEMENT" myself
0
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 11963852
In Delphi you can do simple math with the TDateTime values. The trunc(Now) part is the date-part while the Frac(Now) part is the time part. Something like:

Time3 := Abs(Frac(DateTime1) - Frac(DateTime2));

Then Time3 will be the difference between both times. Using FormatDateTime('HH:NN:SS', Time3) would then provide you the difference in hours, minutes and seconds.

But those are just the Delphi solutions. Solving this in SQL is a bit harder since it depends on the SQL dialect that you're using. SQL is a bit limited in the additional functions it provides. But if I'm not mistaken, many SQL databases do store date/time fields in the same way as Delphi: as floats...

But you might want a refund and re-ask this question in one of the Database-specific areas of EE. It has no relation to Delphi anyway.
0
 
LVL 13

Accepted Solution

by:
BlackTigerX earned 50 total points
ID: 11964851
you could use this:

select convert(varchar, getdate(), 8)

that will give you the time of the current date, of course you can replace getdate with any datetime field you have, and you can assign it to any variable, etc

declare @myvar datetime
set @myvar = convert(varchar, getdate(), 8) --extract the time
select convert(varchar, @myvar, 8) --show the time
0
 
LVL 6

Assisted Solution

by:bpana
bpana earned 30 total points
ID: 11966318
i assume you use SQL Server

declare @date1 datetime, @date2 datetime
declare @hh int, @mm int, @ss int
declare @datediffs int
set @date1 = '2004-09-02 13:42:002'
set @date2 = '2004-09-02 14:43:003'

set @datediffs = datediff(ss, @date1, @date2)
set @hh = round(@datediffs/3600, 0)
set @datediffs = @datediffs - @hh*3600
set @mm = round(@datediffs/60, 0)
set @datediffs = @datediffs - @mm*60
select cast(@hh as varchar(2)) + ':' + cast(@mm as varchar(2)) + ':' + cast(@datediffs as varchar(2))

the result is not nice, but you got the ideea, you can format it as you want
0
 

Author Comment

by:joshfly
ID: 11993155
thanks guys,

:)
josh
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Suggested Courses
Course of the Month9 days, 21 hours left to enroll

624 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