0

I know that SQL Server's DATEDIFF will calculate the difference between two datetime values in a chosen unit - hours, days, etc.

But I want to get the difference between two datetime values more like .Net's DateTimeOffset e.g. more like a date format: years, months, days, hours, minutes.

I don't know if SQL Server 2016 has any equivalent type, or built in functions to do this? Or would I have to roll some custom logic?

Mr. Boy
  • 60,845
  • 93
  • 320
  • 589
  • 1
    You mean like [here](https://stackoverflow.com/questions/57599/how-to-calculate-age-in-t-sql-with-years-months-and-days)? If not, what *are* you asking? Sample data and expected results help us help you. – Thom A Jul 21 '20 at 18:00
  • @Larnu yes same question but I am asking if it can be done in a simple query - or whether T-SQL is required. If it is, this question is probably a dupe. – Mr. Boy Jul 21 '20 at 18:20
  • You want a `TimeSpan`, not a `DateTimeOffset`. The first is a period of time, which is what you get in c# (or any other .Net language for that matter) when subtracting a `DateTime` value from another `DateTime` value, while the second is a `DateTime` for a specific time zone. – Zohar Peled Jul 21 '20 at 18:34
  • There is no `timespan` data type in SQL Server, no. You would have to use something like the above to achieve this in T-sQL. You *could* implement a CLR function, however, you would still be unable to return a `timespan` data type. – Thom A Jul 21 '20 at 18:53
  • I've implemented `timespan` as a CLR data type. It was a fun exercise and I could see it being useful. Not too difficult from what I remember. – Ben Thul Jul 21 '20 at 19:11
  • Unless someone wants to provide a CLR-based answer (I'd be very interested) I will vote to close this as a dupe. – Mr. Boy Jul 22 '20 at 11:07

0 Answers0