SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Vimal Lohani
Vimal Lohani
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 659
Comments posted to this topic are about the item Accurate Time between Two Dates in Year, Month,Day Format

Vimal Lohani
SQL 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
subhanshu-khurana
subhanshu-khurana
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 6
-- nvarchar(15) doesn't give complete value

it should be atleast nvachar(35)
Vimal Lohani
Vimal Lohani
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 659
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 Lohani
SQL 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
ChrisM@Work
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65944 Visits: 20214
-- 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
Exploring Recursive CTEs by Example Dwain Camps
Vimal Lohani
Vimal Lohani
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 659
Thanks ChrisM@Work for your comment. I got the tone.
Generally UDFs are slow as compare to same query.

Vimal Lohani
SQL 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
pappu.dewangan
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 170
Nice and easy!!!!!!!!!!!!!!!!!
Noetic DBA
Noetic DBA
SSChasing Mays
SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)

Group: General Forum Members
Points: 611 Visits: 422
Very useful. Thank you.
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65944 Visits: 20214
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
Exploring Recursive CTEs by Example Dwain Camps
Vimal Lohani
Vimal Lohani
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 659
Sure dear!,
I will use your query, I have tested this. seems good. Thanks once again.
Hope this will helpful to all.

Vimal Lohani
SQL 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
Lee Linares
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1122 Visits: 1509
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search