date to hours - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

date to hours

Author

Topic

amurark
Yak Posting Veteran

55 Posts

Posted - 2011-09-28 : 02:22:07

2010-10-21 14:49:26.000
2010-10-21 14:49:26.000
2010-09-13 18:52:12.000
2010-09-13 18:54:52.000
2010-09-14 14:22:14.000
2010-09-13 13:39:08.000
2010-09-13 13:48:38.000
2010-09-13 19:40:15.000

Ankita

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-28 : 02:22:55

So what is your question?

Best Regards
Vadivel

http://vadivel.blogspot.com

amurark
Yak Posting Veteran

55 Posts

Posted - 2011-09-28 : 02:26:34

convert
2010-10-21 14:49:26.000
2010-09-13 18:52:12.000
2010-09-13 18:54:52.000
2010-09-14 14:22:14.000
2010-09-13 13:39:08.000
2010-09-13 13:48:38.000
2010-09-13 19:40:15.000

convert it into hours
becasue i nedd to find the diffference between two dates in hours'

Ankita

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-28 : 02:27:51

To extract the hour information from a given date make use of DATEPART function.

DECLARE @dt datetime
SET @dt = '2010-10-21 14:49:26.000'
SELECT DATEPART(hh, @dt);

Best Regards
Vadivel

http://vadivel.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 02:30:37

to get difference b/w two dates use

datediff(hh,firstdate,seconddate)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-28 : 02:31:41

quote:
Originally posted by amurark

convert
2010-10-21 14:49:26.000
2010-09-13 18:52:12.000
2010-09-13 18:54:52.000
2010-09-14 14:22:14.000
2010-09-13 13:39:08.000
2010-09-13 13:48:38.000
2010-09-13 19:40:15.000

convert it into hours
becasue i nedd to find the diffference between two dates in hours'

Ankita

Which 2 dates ?

KH
[spoiler]Time is always against us[/spoiler]

amurark
Yak Posting Veteran

55 Posts

Posted - 2011-09-28 : 02:31:53

its not static where i can use @dt = ''2010-10-21 14:49:26.000''

step 1 -i converted the text into datetime as u told by the belwo query '
set dateformat dmy
select cast(convert(varchar(25), comments,111) AS datetime) from ocmqm1

then the output came as

Posted - 09/28/2011 : 02:26:34
convert
2010-10-21 14:49:26.000
2010-09-13 18:52:12.000
2010-09-13 18:54:52.000
2010-09-14 14:22:14.000
2010-09-13 13:39:08.000
2010-09-13 13:48:38.000
2010-09-13 19:40:15.000

now i need the output to be converted as hourws

Ankita

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 02:33:03

you need the difference b/w above dates and current date in hours?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-28 : 02:45:04

If you want to get the difference between two dates you gotto use DATEDIFF as already mentioned in the above post. Thought would show you few variations with that.

DECLARE @stdt DATETIME
SET @stdt = '2011-01-01 10:15:00.000'

Declare @enddt DATETIME
SET @enddt = '2011-01-01 11:35:00.000'

--To get only Hours
SELECT DATEDIFF(hh, @stdt,@enddt ) AS [Hours];

--To get the result in Minutes
SELECT CAST(DATEDIFF(ss, @stdt, @enddt) AS DECIMAL(10, 0)) / 60 AS [Minutes];

--To get both Hours and Minutes
SELECT DATEDIFF(hh, @stdt, @enddt) AS [Hours],
DATEDIFF(mi,DATEADD(hh,DATEDIFF(hh, @stdt, @enddt),@stdt),@enddt) AS [Minutes];

Best Regards
Vadivel

http://vadivel.blogspot.com

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-09-28 : 02:48:58

You can find days difference and then can multiply as shown in below link:
http://www.shabdar.org/sql-server/104-sql-server-calculate-delay-difference-between-two-dates.html

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

amurark
Yak Posting Veteran

55 Posts

Posted - 2011-09-28 : 03:01:14

set dateformat dmy
select cast(convert(varchar(25),substring(comments,4,18),111) as datetime) as close_date ,number,
requested_date, --from ocmqm1
case when number is not null
then datediff(hh,requested_date,cast(convert(varchar(25),substring(comments,4,18),111) as datetime)
)

else ''
end as diff_hours

from ocmqm1
order by diff_hours

done

thnks to everyone

and spcl thnks to vmvadivel

Ankita

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-28 : 03:12:48

You are welcome Ankita :)

Best Regards
Vadivel

http://vadivel.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources