2

I need to subtract 2 days from each other and display it in YYMMDD.

E.g.

2016-05-27 (Minus) 2015-01-15 = 1 Years, 5 Months, 16 Days

The two dates are in DATETIME format.

Many thanks!

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
Noost
  • 21
  • 2
  • 1
    Have you tried anything yourself? – MarengoHue Mar 01 '16 at 09:31
  • 1
    Dates do not have formats, they are binary. `DATEFORMAT` applies only to formatting/parsing strings and should be avoided. You may have any number of problems, eg using strings instead of dates, using an inappropriate string format, using the wrong function. Please post the code. In any case, there is *no* type that can return a time span in the way you ask – Panagiotis Kanavos Mar 01 '16 at 09:46
  • I tried various combinations using DATEDIFF and DATEADD but it will always run over, e.g. 91 days and not e.g. 1day 3 Months. If this is making sense? – Noost Mar 01 '16 at 10:01
  • 1
    @Noost there's no runover, that's the expected result. You are asking about age calculation, not the difference between two dates. You can't calculate age from the date difference - in your example, how long is each month? 28,29,30 or 31 days? How long is that 1 year? Obviously, it depends on leap years, the calendar used etc. There *are* a lot of relevant SO questions and many techniques, as it's not straighforward to calculate age in SQL – Panagiotis Kanavos Mar 01 '16 at 10:11
  • @Panagiotis, thanks for the help thus far. I do realise this is not straight forward as I do indeed need age calculation. I will check out the duplicate above. Thanks – Noost Mar 01 '16 at 10:35
  • @Noost remember, 4 years ago Azure crashed because someone added 365 to a certificate date instead of 1 year. You may want to do the calculations on the client side,e g. using and appropriate Calendar class or library – Panagiotis Kanavos Mar 01 '16 at 10:41

3 Answers3

1

I think this works.

SELECT Cast(DATEDIFF(YEAR, '2015-01-15', '2016-05-27') as varchar(25)) + ' Years, '
    + Cast(DATEDIFF(MONTH, '2015-01-15', '2016-05-27') % 12 as varchar(25)) + ' Months, '
    + Cast(DATEDIFF(DAY, Dateadd(Month, DATEDIFF(MONTH, '2015-01-15', '2016-05-27')  % 12 ,Dateadd(Year, DATEDIFF(YEAR, '2015-01-15', '2016-05-27'), '2015-01-15')), '2016-05-27') as varchar(25)) + ' Days'

Result:

1 Years, 4 Months, 12 Days
Kim
  • 771
  • 6
  • 23
0

You might take a look at this documentation https://msdn.microsoft.com/en-us/library/ms189794.aspx

DATEDIFF ( datepart , startdate , enddate )

Then get the date with the number of Days

Then you can use DATEADD to add this number of Days.

SELECT DATEADD(dd, 0, DATEDIFF(dd, startdate, enddate))

Didier Aupest
  • 3,227
  • 2
  • 23
  • 35
  • Hi, tried your code, this is the result: Date1: 2015-08-01 00:00:00.000 Date2: 2016-01-25 13:45:01.437 Days1: 1900-06-27 00:00:00.000 MONTH: 1900-01-06 00:00:00.000 YEAR: 1900-01-02 00:00:00.000 – Noost Mar 01 '16 at 10:11
  • 1
    @Noost make sure you use the `datetime2` type instead of `datetime`. 0 for datetime is 0001-01-01 while 0 for datetime is 1900-01-01. This is a clever hack in any case that doesn't account for leap years. You are asking about age calculation and you can't do that with a simple DATEDIFF – Panagiotis Kanavos Mar 01 '16 at 10:18
0

Try this query...

 select (datediff(dd,convert(datetime,'5/5/2014',101),getdate())/365) years, 
((datediff(dd,convert(datetime,'5/5/2014',101),getdate()) % 365)/30) months, 
((datediff(dd,convert(datetime,'5/5/2014',101),getdate()) % 365)%30) days

Result

years   months  days
1        10      1
Sankar
  • 6,908
  • 2
  • 30
  • 53