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

Question

Post reply

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

Vimal Lohani

SSCommitted

Points: 1650
More actions
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

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

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

subhanshu-khurana Newbie Points: 9 More actions · Answer 1

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

it should be atleast nvachar(35)

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 2

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

-- 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

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 4

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

pappu.dewangan SSC Enthusiast Points: 160 More actions · Answer 5

pappu.dewangan

SSC Enthusiast

Points: 160

August 13, 2014 at 6:42 am

#1737560

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

Noetic DBA SSC Eights! Points: 938 More actions · Answer 6

Noetic DBA

SSC Eights!

Points: 938

August 13, 2014 at 8:00 am

#1737589

Very useful. Thank you.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

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

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 8

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

Lee Linares SSCrazy Points: 2723 More actions · Answer 9

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

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 10

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

Lee Linares SSCrazy Points: 2723 More actions · Answer 11

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

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 12

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

Lee Linares SSCrazy Points: 2723 More actions · Answer 13

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

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 14

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