Post reply

Accurate Time between Two Dates in Year, Month,Day Format

  • August 12, 2014 at 8:45 pm

    #289037

    Comments posted to this topic are about the item Accurate Time between Two Dates in Year, Month,Day Format

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

  • August 12, 2014 at 11:40 pm

    #1737439

    -- nvarchar(15) doesn't give complete value

    it should be atleast nvachar(35)

  • August 13, 2014 at 12:25 am

    #1737445

    subhanshu-khurana (8/12/2014)

    -- nvarchar(15) doesn't give complete value

    it should be atleast nvachar(35)

    you are right subhanshu-khurana

    I have already send request for this.

    Thanks for the comment.

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

  • August 13, 2014 at 4:17 am

    #1737497

    -- Your function returns incorrect values when the time of the from-date is later than the time of the to-date.

    -- Also, it's likely to be far faster written as an inline table-valued function, like this:

    SELECT

    CAST(Years AS VARCHAR(4)) + ' Years :' + CAST(Months AS VARCHAR(2)) + ' Months :' + CAST([Days] AS VARCHAR(2)) + ' Days'

    FROM ( -- f

    SELECT

    Years, Months, [Days] = DATEDIFF(DAY,DATEADD(MONTH,Months,DATEADD(YEAR,Years,@Fromdate)),@Todate)

    - CASE WHEN DATEADD(DAY,DATEDIFF(DAY,DATEADD(MONTH,Months,DATEADD(YEAR,Years,@Fromdate)),@Todate),DATEADD(MONTH,Months,DATEADD(YEAR,Years,@Fromdate))) > @Todate THEN 1 ELSE 0 END

    FROM ( -- e

    SELECT Years, [Months] = DATEDIFF(MONTH,DATEADD(YEAR,Years,@Fromdate),@Todate)

    - CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH,DATEADD(YEAR,Years,@Fromdate),@Todate),DATEADD(YEAR,Years,@Fromdate)) > @Todate THEN 1 ELSE 0 END

    FROM ( -- d

    SELECT

    [Years] = DATEDIFF(YEAR,@Fromdate,@Todate) - CASE WHEN DATEADD(YEAR,DATEDIFF(YEAR,@Fromdate,@Todate),@Fromdate) > @Todate THEN 1 ELSE 0 END

    ) d

    ) e

    ) f

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • August 13, 2014 at 4:30 am

    #1737498

    Thanks ChrisM@Work for your comment. I got the tone.

    Generally UDFs are slow as compare to same query.

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

  • August 13, 2014 at 6:42 am

    #1737560

    Nice and easy!!!!!!!!!!!!!!!!!

  • August 13, 2014 at 8:00 am

    #1737589

    Very useful. Thank you.

  • August 13, 2014 at 8:40 am

    #1737619

    Vimal Lohani (8/13/2014)

    Thanks ChrisM@Work for your comment. I got the tone.

    Generally UDFs are slow as compare to same query.

    You may also wish to correct the error. Your function returns incorrect values when the time of the from-date is later than the time of the to-date - use the dates from my last post to check. Changing the input parameters to DATE might fix this - it's worth a try.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • August 13, 2014 at 10:27 pm

    #1737903

    Sure dear!,

    I will use your query, I have tested this. seems good. Thanks once again.

    Hope this will helpful to all.

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

  • August 14, 2014 at 2:11 pm

    #1738215

    Thanks for taking the time to create and share your code. I found that when the @FromDate was more recent than the @ToDate the returned answer was incorrect. I simplified your script and corrected this issue. Thanks again.

    Lee

    Here is the modified script:

    CREATE Function [dbo].[fn_TotaltimeBetweendates] (@Fromdate datetime, @Todate datetime )

    Returns nvarchar(40)

    as

    Begin

    RETURN

    (

    SELECT

    CAST(Years AS VARCHAR(4)) + ' Years :' +

    CAST(ABS(Months) AS VARCHAR(2)) + ' Months :' +

    CAST(ABS([Days]) AS VARCHAR(2)) + ' Days'

    FROM

    ( -- f

    SELECT

    Years,

    Months,

    [Days] = DATEDIFF( DAY,DATEADD(MONTH,Months,DATEADD(YEAR,Years,@Fromdate)),@Todate)

    FROM

    ( -- e

    SELECT

    Years,

    [Months] = DATEDIFF(MONTH,DATEADD(YEAR,Years,@Fromdate),@Todate)

    FROM ( -- d

    SELECT

    [Years] = DATEDIFF(YEAR,@Fromdate,@Todate)

    ) d

    ) e

    ) f

    )

    End

  • August 17, 2014 at 10:24 pm

    #1738704

    Lee Linares (8/14/2014)

    Thanks for taking the time to create and share your code. I found that when the @FromDate was more recent than the @ToDate the returned answer was incorrect. I simplified your script and corrected this issue. Thanks again.

    Lee

    Here is the modified script:

    Hi Lee, Could you give me some example or some dates, so that i can test it, where it is going wrong.

    although thanks for the code.

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

  • August 19, 2014 at 6:33 am

    #1739185

    Sure, here are the results of 2 calls using your posted code:

    Select [dbo].[fn_TotaltimeBetweendates] ('06/13/1990', '08/18/2014') -- Returns 24 Years :2 Months :5 Days

    Select [dbo].[fn_TotaltimeBetweendates] ('08/18/2014', '06/13/1990') -- Returns -25 Years :9 Months :26 Days

  • August 19, 2014 at 7:05 am

    #1739194

    Lee Linares (8/19/2014)

    Sure, here are the results of 2 calls using your posted code:

    Select [dbo].[fn_TotaltimeBetweendates] ('06/13/1990', '08/18/2014') -- Returns 24 Years :2 Months :5 Days

    Select [dbo].[fn_TotaltimeBetweendates] ('08/18/2014', '06/13/1990') -- Returns -25 Years :9 Months :26 Days

    I can see that, the problem is in way of representation or way to see::isn't it,

    see (25 years-9month-26 days ) =-(24 years+2month+5days)

    or -(25 years-9month-26 days ) =(24 years+2month+5days)

    you got my point. Please comment, if you see something else....

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

  • August 19, 2014 at 7:20 am

    #1739205

    I see your point. But the end-user would be confused by the output.

    In reality if I were deploying this I would likely disallow the user from inputting a @Fromdate greater than the @Todate.

    Thanks again.

    Lee

  • August 19, 2014 at 9:00 am

    #1739253

    Lee Linares (8/19/2014)

    I see your point. But the end-user would be confused by the output.

    In reality if I were deploying this I would likely disallow the user from inputting a @Fromdate greater than the @Todate.

    Thanks again.

    Lee

    i got your point but this is a case when he will write the value of @fromdate to @todate by mistake. He should know @fromdate<=@todate.

    by the way,what do you thing, how should i represent the answer. In your case.

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

Viewing 15 posts - 1 through 15 (of 27 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy