April 13, 2009 at 9:13 am
Hello,
I have two datetime fields and I want to find a difference between those two in either hh:mm:ss or hh:mm format.
Is there a way to achieve that in SQL?
The query is:
SELECT
min(starttime) as Minimum,
max(endtime) as Maximum,
min(starttime) - max(endtime) as Difference
FROM [DatabaseName].[dbo].[TableName]
WHERE
starttime
BETWEEN
'2009-04-13 00:00:00.000'
AND
'2009-04-13 08:30:00.000'
Can you please help me modify the query so that I can get the results in either hh:mm:ss format or hh:mm format.
Thank you.
April 13, 2009 at 9:16 am
Do you still want the results in HH:MM or HH:MM:SS format if the hours exceeds 24 hours? For example 2009-04-12 10:00:00 to 2009-04-13 10:05:00 would be 24:05.
April 13, 2009 at 9:35 am
Yes. I want it in HH:MM:SS format, because most of the times I will be having less than 24 hours, except for some cases. So, thats fine with me.
April 13, 2009 at 9:47 am
Does this help you get started?
declare @Date1 datetime,
@Date2 datetime;
select
@Date1 = '2009-04-13 10:00:00',
@Date2 = '2009-04-13 10:15:16';
select
datediff(ss, @Date1, @Date2),
datediff(ss, @Date1, @Date2) % 60 as Seconds,
(datediff(ss, @Date1, @Date2) / 60) % 60 as Minutes,
(datediff(ss, @Date1, @Date2) / (60 * 60)) % 60 as Hours
;
April 13, 2009 at 9:49 am
Thank you Lynn.
I got one more help which mentioned:
SELECT
MIN(starttime) as Minimum,
MAX(endtime) as Maximum,
CONVERT
(varchar,(MIN(starttime) - MAX(endtime)),108) as Diff
FROM
dbo.TableName
WHERE
startime
BETWEEN
'2009-04-13 00:00:00.000'
AND
'2009-04-13 08:30:00.000'
This is also working fine.
April 13, 2009 at 9:56 am
Now, I want to store the results of this query in a variable in SSIS.
But I am getting the results as: 16:52:45
I do not understand the datatype that I can have for storing this as a variable in SSIS.
Any help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply