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.
Author |
Topic |
soni
Starting Member
1 Post |
Posted - 2012-10-15 : 01:38:51
|
i have 2 date columns date1 and date2(date1- date2) which has date in 12 hr formatfor ex : Date1 : 10/3/2012 12:02:16 PM (mm/dd/yyyy hh:mm:ss)Date2 : 9/21/2012 12:01:32 AM (mm/dd/yyyy hh:mm:ss)i need to get the following output: 300:00:44 (hh:mm:ss)Plz helpThnkz in advance!!!!!!!!!!! |
|
sql-programmers
Posting Yak Master
190 Posts |
Posted - 2012-10-15 : 01:43:34
|
declare @date1 AS DATETIMEdeclare @date2 AS DATETIMEDECLARE @diff AS INTSET @date1 = CONVERT(datetime, '10/3/2012 12:02:16 PM')SET @date2 = CONVERT(datetime, '9/21/2012 12:01:32 AM')SET @diff = DATEDIFF(SS, @date1, @date2)SET @diff = CASE WHEN @diff < 0 THEN @diff * -1 ELSE @diff END SELECT (CASE WHEN LEN((@diff/3600))>1 THEN CONVERT(VARCHAR, (@diff/3600)) ELSE '0' + CONVERT(VARCHAR, (@diff/3600)) END) + ':' + (CASE WHEN LEN(((@diff%3600)/60))>1 THEN CONVERT(VARCHAR, ((@diff%3600)/60)) ELSE '0' + CONVERT(VARCHAR, ((@diff%3600)/60)) END ) + ':' + (CASE WHEN LEN(((@diff%3600)%60))>1 THEN CONVERT(VARCHAR, ((@diff%3600)%60)) ELSE '0' + CONVERT(VARCHAR, ((@diff%3600)%60)) END ) SQL Server Programmers and Consultantshttp://www.sql-programmers.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-15 : 11:12:15
|
[code]declare @Date1 datetime ='10/3/2012 12:02:16 PM'declare @Date2 datetime = '9/21/2012 11:01:32 PM'SELECT STUFF( CONVERT(varchar(8),timediff,108),1,CHARINDEX(':',CONVERT(varchar(8),timediff,108))-1,DATEDIFF(dd,0,timediff) * 24 + DATEPART(hh,timediff))FROM(SELECT DATEADD(ss,ABS(DATEDIFF(ss,@Date1,@Date2)),0) AS timediff)t[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|