datediff hh:mm:ss 1

[le1234]

i need to be able to calculate the difference between to dates and show the results as hh:mm:ss

 

[bborissov]

[COLOR=blue]DECLARE[/color] @Test [COLOR=#FF00FF]datetime[/color]
[COLOR=blue]SET[/color] @Test = [COLOR=red]'20061112'[/color]
[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]DateDiff[/color](ss, @test, [COLOR=#FF00FF]GETDATE[/color]()) [COLOR=blue]AS[/color] DTDiff

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]CAST[/color]([COLOR=#FF00FF]CAST[/color](Tbl1.DTDiff/3600 [COLOR=blue]as[/color] [COLOR=blue]int[/color]) [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](20))+[COLOR=red]':'[/color]+
       [COLOR=#FF00FF]RIGHT[/color]([COLOR=red]'00'[/color]+[COLOR=#FF00FF]CAST[/color]([COLOR=#FF00FF]CAST[/color]((Tbl1.DTDiff - [COLOR=#FF00FF]CAST[/color](Tbl1.DTDiff/3600 [COLOR=blue]as[/color] [COLOR=blue]int[/color])*3600)/60 [COLOR=blue]as[/color] [COLOR=blue]int[/color]) [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](2)),2)+[COLOR=red]':'[/color]+
       [COLOR=#FF00FF]RIGHT[/color]([COLOR=red]'00'[/color]+[COLOR=#FF00FF]CAST[/color](Tbl1.DTDiff%60 [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](20)),2)
[COLOR=blue]FROM[/color] ([COLOR=blue]SELECT[/color] [COLOR=#FF00FF]DateDiff[/color](ss, @test, [COLOR=#FF00FF]GETDATE[/color]()) [COLOR=blue]AS[/color] DTDiff) Tbl1

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ca8msm]

You could also use this method:

[COLOR=blue]Declare[/color] @Start [COLOR=#FF00FF]DateTime[/color]
[COLOR=blue]Declare[/color] @End [COLOR=#FF00FF]DateTime[/color]
[COLOR=blue]Declare[/color] @Difference [COLOR=#FF00FF]DateTime[/color]

[COLOR=blue]Set[/color] @Start = [COLOR=red]'06/18/2007 8:00:00 AM'[/color]
[COLOR=blue]Set[/color] @End = [COLOR=red]'06/18/2007 11:41:45 AM'[/color]
[COLOR=blue]Set[/color] @Difference = @End - @Start

[COLOR=blue]Select[/color] [COLOR=#FF00FF]DATEPART[/color](hh, @Difference), [COLOR=#FF00FF]DATEPART[/color](mi, @Difference), [COLOR=#FF00FF]DATEPART[/color](ss, @Difference)

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[le1234]

thanks ca8msm, i am trying to put this method into a query so that the difference will be calculated for the multiple rows returned. So i put the field name in the set statement instead of a specified date, but I get an error......can you recommend another way?

 

[ca8msm]

If you are doing it as part of your SQL Statement, you can just calculate the difference by subtracting one date field from the other e.g.

[COLOR=blue]SELECT[/color] 
	[COLOR=#FF00FF]DATEPART[/color](hh, datetwo - dateone),
	[COLOR=#FF00FF]DATEPART[/color](mi, datetwo - dateone), 
	[COLOR=#FF00FF]DATEPART[/color](ss, datetwo - dateone)
[COLOR=blue]FROM[/color] TABLENAME

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[le1234]

that was exactly what i needed.
Many Thanks