Post reply

Date Difference Question

  • November 10, 2009 at 7:25 am

    #142979

    I have two fields in a table one for start date and one for end date. The first request I had was to determine the amount of time that has passed between the dates in days which was no problem. I used DATEDIFF(d, field1,field2). Now instead of just a number of days they would like it to read like the following:

    years , months, days

    For example if the number of days is 396 then they want it to read

    1 year, 1 month 1 day

    Anyone have any suggestions?

  • November 10, 2009 at 9:27 am

    #1077350

    How many days does a year have in your scenario?

    same question for month...

    Your example could also result in 1 year 0 month 30 days (assuming a year with February 29th) or 1 year 1 month 3 days (counting 396 days from Feb. 1st 2009) when based on "real" calendar.

    Depending on the requirement (example: 30 day per month, 12 month per year = 360 days per year) you could use something like

    SELECT (DATEDIFF(day,@field1,@field2)/360)

    SELECT (DATEDIFF(day,@field1,@field2)/30)%12

    SELECT DATEDIFF(day,@field1,@field2)%30

    Edit: SQL code modified.

    Edit2: question in general: Why don't you format the date on the presentation layer? That's usually the better way to do it...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply