Difference between two datetime fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Difference between two datetime fields

Author

Topic

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-04-13 : 10:12:31

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.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-04-13 : 10:18:24

Try this.

SELECT
min(convert(varchar,startime,108)) as Minimum,
max(convert(varchar,endtime,108)) as Maximum,
min(convert(varchar,startime,108) - max(convert(varchar,endtime,108)) as Difference
FROM [DatabaseName].[dbo].[TableName]
WHERE
starttime
BETWEEN
'2009-04-13 00:00:00.000'
AND
'2009-04-13 08:30:00.000'

Karthik

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-04-13 : 10:34:07

quote:
Originally posted by karthik_padbanaban

Try this.

SELECT
min(convert(varchar,startime,108)) as Minimum,
max(convert(varchar,endtime,108)) as Maximum,
min(convert(varchar,startime,108) - max(convert(varchar,endtime,108)) as Difference
FROM [DatabaseName].[dbo].[TableName]
WHERE
starttime
BETWEEN
'2009-04-13 00:00:00.000'
AND
'2009-04-13 08:30:00.000'

Karthik

Karthik, thank you for your effort, but this query does not work.
It is having syntax errors in:
min(convert(varchar,startime,108) - max(convert(varchar,endtime,108)) as Difference

If I do not have this line in the query, I am getting the min and the max times, but the difference does now work.

Thank you.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-04-13 : 11:00:21

Oh sorry then this may work for you.

SELECT
min(convert(varchar,startime,108)) as Minimum,
max(convert(varchar,endtime,108)) as Maximum,
convert(varchar,(min(endtime) - max(startime)),108) as Difference
FROM @a
WHERE
startime
BETWEEN
'2009-04-13 00:00:00.000'
AND
'2009-04-13 21:50:00.000'

Karthik

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-04-13 : 11:43:08

quote:
Originally posted by karthik_padbanaban

Oh sorry then this may work for you.

SELECT
min(convert(varchar,startime,108)) as Minimum,
max(convert(varchar,endtime,108)) as Maximum,
convert(varchar,(min(endtime) - max(startime)),108) as Difference
FROM @a
WHERE
startime
BETWEEN
'2009-04-13 00:00:00.000'
AND
'2009-04-13 21:50:00.000'

Karthik

Its working fine. Thank you so much.

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-04-13 : 11:57:33

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!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-13 : 11:58:25

String wont do?

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-04-13 : 13:09:18

quote:
Originally posted by vijayisonly

String wont do?

I tried it. Its not working. Actually, I want to have a full result set and for that its showing some errors.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources