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)
 find AM hours and PM hours between two datetime

Author  Topic 

turbo
Starting Member

19 Posts

Posted - 2011-04-14 : 08:55:09
i have two datetime values say
@a : 2011-04-14 11:00:05.170
@b : 2011-04-14 14:51:05.170

so AM hours is from 11hrs to 12 hrs
and PM hours is from 12hrs to 14 hours
I need query to show the total AM hours and PM hours ...

Kindly help
Thks in Advance .....

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-14 : 09:05:03
datediff(hh,@startdate,'2011-04-14 12:00:00') AS AMHours
datediff(hh,'2011-04-14 12:00:00',@EndDate) as PMHours

You can use some date manipulation on either the start or the end to work out midday.
Question, what happens if one of those dates is on a different day?

--
Gail Shaw
SQL Server MVP
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-14 : 09:07:00
declare @a datetime = '20110414 11:00:05.170', @b datetime = '20110414 14:51:05.170'

select DATEDIFF(hh,@a,convert(varchar(11),@a,127)+'12:00:00.000') ,
DATEDIFF(hh,convert(varchar(11),@b,127)+'12:00:00.000', @b)


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

turbo
Starting Member

19 Posts

Posted - 2011-04-14 : 09:27:06
Thks a lot .. works good :-)
Go to Top of Page
   

- Advertisement -