Create and return a date string from calculated years, months, and days

Transact-SQL
1

I am trying to create a string that returns something like "1 year, 2 months, and 5 days", or "5 months and 1 day" or "6 days" depending on the number of years, months, and days calculated. I can't figure a "simple" way. Would someone please post a suggestion? TIA
Bill

Select DateDiff(dd, r.DateOpened, r.DateClosed) As DaysOpen, 
 DateDiff(dd, r.DateOpened, r.DateClosed)/365 as "Years",
(DateDiff(dd, r.DateOpened, r.DateClosed) % 365 /30) as "Months",
(DateDiff(dd, r.DateOpened, r.DateClosed) % 365) % 30 as "Days"
    -- now string all of this together to return example above to return something like: 
          1 year, 2 months, and 5 days", or "5 months and 1 day" or "6 days"
2

I thought the accurate year/month/day difference was the hard part. But if you need adding the strings around the values, just let me know.

The calcs are in the CROSS APPLYs, so that previously calc'd values can be used in subsequent calcs.

There are two ways to calc this: not counting the open day or counting the open day (second month): take your pick.

--not counting DateOpened separately
SELECT *
FROM ( VALUES (CAST('2017-11-04' AS date),CAST(GETDATE() AS date)),
              ('20170101', '20171231'),
              ('20170101', '20180101') ) 
    AS test_data( DateOpened, DateClosed )
CROSS APPLY (
    SELECT DATEDIFF(YEAR, DateOpened, DateClosed) -
        CASE WHEN CONVERT(char(5), DateOpened, 1) > 
                  CONVERT(char(5), DateClosed, 1) THEN 1 ELSE 0 END AS years_old
) AS year_calc
CROSS APPLY (
    SELECT DATEDIFF(MONTH, DateOpened, DateClosed) - (years_old * 12) -
        CASE WHEN DAY(DateOpened) > DAY(DateClosed) THEN 1 ELSE 0 END AS months_old
) AS month_calc
CROSS APPLY (
    SELECT DATEDIFF(DAY, DATEADD(MONTH, years_old * 12 + months_old, DateOpened),
        DateClosed) AS days_old
) AS day_calc

--counting DateOpened separately
SELECT *
FROM ( VALUES (CAST('2017-11-04' AS date),CAST(GETDATE() AS date)),
              ('20170101', '20171231'),
              ('20170101', '20180101') ) 
    AS test_data( DateOpened, DateClosed )
CROSS APPLY (
    SELECT DATEDIFF(YEAR, DateOpened, DATEADD(DAY, 1, DateClosed)) -
        CASE WHEN CONVERT(char(5), DateOpened, 1) > 
                  CONVERT(char(5), DATEADD(DAY, 1, DateClosed), 1) THEN 1 ELSE 0 END AS years_old
) AS year_calc
CROSS APPLY (
    SELECT DATEDIFF(MONTH, DateOpened, DATEADD(DAY, 1, DateClosed)) - (years_old * 12) -
        CASE WHEN DAY(DateOpened) > DAY(DateClosed) THEN 1 ELSE 0 END AS months_old
) AS month_calc
CROSS APPLY (
    SELECT DATEDIFF(DAY, DATEADD(MONTH, years_old * 12 + months_old, DateOpened),
        DATEADD(DAY, 1, DateClosed)) AS days_old
) AS day_calc
3

--create table dates
--(
--DateOpened datetime,
--DateClosed datetime
--)
--insert into dates values('2017-11-04',getdate())
--insert into dates values('2017-01-01','2017-12-31')
--insert into dates values('2017-01-01','2018-01-01')

Select *,
case when DateDiff(year,DateOpened,DateClosed)>1 then cast(DateDiff(year,DateOpened,DateClosed) as varchar) +' years' else cast(DateDiff(year,DateOpened,DateClosed) as varchar) + ' Year' end as "Years",
case when DateDiff(month,DateOpened,DateClosed)>1 then cast(DateDiff(month,DateOpened,DateClosed) as varchar) + ' months' else cast(DateDiff(month,DateOpened,DateClosed) as varchar) + ' month' end as "Months",
case when DateDiff(dd,DateOpened,DateClosed)>1 then cast(DateDiff(dd,DateOpened,DateClosed) as varchar) +' days' else cast(DateDiff(dd,DateOpened,DateClosed) as varchar) + ' day' end as "Days"
from dates

5

In order to get months you can use datediff(month,date1,date2)
But whats the logic in below statement. Could you please explain that.
DateDiff(dd, r.DateOpened, r.DateClosed) % 365 /30) as "Months",

6

hi

please see the following article

hope it helps
:slight_smile:
:slight_smile:

7

Thanks to all who replied. I can get and have worked out the date calculations. But, what I needed was how to return that like this, as a single string:
"1 year, 2 months, and 5 days",
or
"5 months and 1 day"
or
"6 days"
depending on the number of years, months, and days calculated

8
this is how 
Cast ( Your years calc as varchar)
+ ' years '  +
Cast ( Your months calc as varchar)
+ ' months' +
Cast ( Your days calc as varchar)
+ ' days'
9

harishgg1:
Thanks, that's what I was looking for

this is how

Cast ( Your years calc as varchar)
+ ' years '  +
Cast ( Your months calc as varchar)
+ ' months' +
Cast ( Your days calc as varchar)
+ ' days'

Reply

Bookmark Share Flag Reply

Watching

You will receive notifications because you created this topic.

Suggested Topics

Topic Replies Views Activity
SP with 4 select statements not returning records from one of the 4 issue -VB.NET 1

Transact-SQL|8|544|Apr '17|
|Get latest record for each customer 1

Transact-SQL|6|707|May '16|
|Get max value from datareader results 1|1|293|Nov '16|
|Close id gaps

Transact-SQL|3|13|6m|
|Add a running number column based on a text value

Transact-SQL

tsql|3|31|8m|

There are 3 unread and 13 new topics remaining, or browse other topics in Transact-SQL

10

Maybe you Should try using a remote employee monitoring software,instead of calculating working process on your own? I agree that this is part of your job/training in software development, but it's better use a well known, tried and tested software, especially if you're calculating your own hours and salary.