-1

I've got two dates:

2016-11-10 18:30:00
1901-01-01 19:00:00

The difference in time I need to return is 30 minutes, so .5 of an hour. I have absolutely no concern for the dates.

I do realize this is a common question but I'm having difficulty applying the answers I've found to my specific issue.

Any help appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MPJ567
  • 521
  • 5
  • 16
  • Possible duplicate of [How to compare two dates to find time difference in SQL Server 2005, date manipulation](http://stackoverflow.com/questions/9521434/how-to-compare-two-dates-to-find-time-difference-in-sql-server-2005-date-manipu) – jmoerdyk Jan 13 '17 at 23:59
  • @jmoerdyk I'm either misreading the answers provided in your link and cannot figure out the answer or you've misunderstood my question. I need the difference between only the hour portion of my dates. – MPJ567 Jan 14 '17 at 00:18
  • Have you read the documentation for the `DATEDIFF` function? You're going to use either `HOURS` or `MINUTES` as the `datepart` parameter. – jmoerdyk Jan 14 '17 at 00:27
  • Do you mean you only care about the Time part rather than the Date part of the DateTimes? – GantTheWanderer Jan 14 '17 at 01:43
  • @GantTheWanderer That is correct. Given the dates I posted, the answer I'm looking for is 30 minutes. Ideally I'd prefer this to be represented as a fraction of an hour, .50. – MPJ567 Jan 14 '17 at 17:08
  • @jmoerdyk As I understand it from the documentation datediff is the difference between two dates in the unit specific by datepart. So for example datediff(hour, '1901-01-01 19:00:00.000', '2016-11-01 18:30:00.000') = 1015391. This is 115 years. I am looking for the answer, .5 of an hour. I apologize I don't know how to make this more clear for you. – MPJ567 Jan 14 '17 at 17:19

3 Answers3

1

This should point you in a suitable direction:

declare @Foo as DateTime = '2016-11-10 18:30:00',
  @Bar as DateTime = '1901-01-01 19:00:00';
select @Foo as Foo, @Bar as Bar,
  Cast( @Foo as Time ) as FooTime, Cast( @Bar as Time ) as BarTime,
  DateDiff( minute, Cast( @Foo as Time ), Cast( @Bar as Time ) ) as DeltaMinutes;
HABO
  • 15,314
  • 5
  • 39
  • 57
  • Thanks for the assist, but this comes out to 690. Am I missing something from your explanation, or some fundamental aspect of how DateDiff is operating that I'm not understanding? – MPJ567 Jan 14 '17 at 17:09
  • @MPJ567 What do you get if you replace the date/times with `'2016-11-10T18:30:00'` and `'1901-01-01T19:00:00'`? Those are in ISO format that should be immune to internationalization issues. Are the intermediate results the expected values, i.e. `FooTime` is `18:30:00.0000000`? – HABO Jan 14 '17 at 18:51
  • I must have done something wrong the first time I tried this. Attempted a second time and alls well. Your help was much appreciated. – MPJ567 Jan 15 '17 at 16:33
0

I do not know that it covers all situations but you can do as follows.

DECLARE @Val1 DATETIME = '2016-11-10 18:30:00'
DECLARE @Val2 DATETIME = '1901-01-01 19:00:00'

SELECT ABS(DATEDIFF(MINUTE, RIGHT(@Val2, 8), RIGHT(@Val1, 8))) -- 30
neer
  • 4,031
  • 6
  • 20
  • 34
  • You could use `7` rather than `8` for the substring lengths since the default format for casting a `DATETIME` to a `VARCHAR` is "mon dd yyyy hh:miAM (or PM)". ([Ref](https://msdn.microsoft.com/en-us/library/ms187928.aspx). If you `select Right( @Val1, 19 ), Right( @Val1, 8 )` you'll see the extra space (and missing seconds). Rather than making a round trip to text and back, and due to the fun that can result from internationalization, it's more reliable to stick with numerically processing dates and times. – HABO Jan 14 '17 at 14:33
  • This also comes out to 690, which another answer provided by @Habo did as well. I'm guessing I'm missing something really simple here. Any further clarity would be appreciated. – MPJ567 Jan 14 '17 at 17:11
0

I too faced a similar scenario in Teradata-SQL where the time was in Integer, so difference can be taken as follows:

SELECT CAST(CAST((END_TM-STRT_TM)AS INTEGER FORMAT '99:99:99') AS INTEGER FORMAT'99:99') as New_Diff FROM TABLE
Albab A. Khan
  • 83
  • 1
  • 1
  • 6