April 18, 2018 at 9:05 am
Hi Everyone,
After reading on this topic and being advised to use DateDiff I wrote a Function that doesn't provide the answer I want. The client wants to now how long it took to complete a checklist. I have a CreationDate and CompletionDate. I need to know how many years, months, weeks and days it took. If it is 2 days then '2 days' without the years. The function deducts the number of years and then attempt to check the number of months, then the number of weeks and then the number of days. Only results are given if available. It seems DateDiff is the problem...or I am the problem not understanding DateDiff. It even returns a week for a 4 day difference in dates which doesnt make sense. It should return the number of weeks within the two dates, not caring when it starts.
Below the code
ALTER FUNCTION [dbo].[DateRangeText](@FromDate DATETIME, @ToDate DATETIME)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Result AS VARCHAR(MAX);
SET @Result = '';
DECLARE @TmpS AS VARCHAR(MAX);
SET @TmpS = '';
DECLARE @Years AS INT;
SET @Years = DATEDIFF(year, @FromDate, @ToDate);
IF (@Years > 0)
BEGIN
IF (@Years = 1)
SET @TmpS = ' Year ';
ELSE
SET @TmpS = ' Years ';
SET @Result = @Result + CAST(@Years AS VARCHAR) + @TmpS;
SET @ToDate = DATEADD(YEAR, -1 * @Years, @ToDate);
END;
DECLARE @Months AS INT;
SET @Months = DATEDIFF(month, @FromDate, @ToDate);
IF (@Months > 0)
BEGIN
IF (@Months = 1)
SET @TmpS = ' Month ';
ELSE
SET @TmpS = ' Months ';
SET @Result = @Result + CAST(@Months AS VARCHAR) + @TmpS;
SET @ToDate = DATEADD(MONTH, -1 * @Months, @ToDate);
END;
DECLARE @Weeks AS INT;
SET @Weeks = DATEDIFF(week, @FromDate, @ToDate);
IF (@Weeks > 0)
BEGIN
IF (@Weeks = 1)
SET @TmpS = ' Week ';
ELSE
SET @TmpS = ' Weeks ';
SET @Result = @Result + CAST(@Weeks AS VARCHAR) + @TmpS;
SET @ToDate = DATEADD(WEEK, -1 * @Weeks, @ToDate);
END;
DECLARE @Days AS INT;
SET @Days = DATEDIFF(day, @FromDate, @ToDate);
IF (@Days > 0)
BEGIN
IF (@Days = 1)
SET @TmpS = ' Day ';
ELSE
SET @TmpS = ' Days ';
SET @Result = @Result + CAST(@Days AS VARCHAR) + @TmpS;
SET @ToDate = DATEADD(WEEK, -1 * @Days, @ToDate);
END;
IF (@Result = '')
SET @Result = 'Same day';
RETURN Rtrim(COALESCE(@Result,''));
END;
April 18, 2018 at 9:17 am
You haven't provided an example of a pair of dates for which the function doesn't work, so I'll guess what the problem is. The way DATEDIFF works is by counting the number of boundaries crossed between two date or datetime values. So two dates four days apart may be interpreted as being 0 weeks or 1 week apart, depending on whether the period between them includes your local DATEFIRST day. The best way round this would be to calculate days directly after months, then divide by 7 to get the number of weeks.
John
April 18, 2018 at 9:19 am
DateDiff is behaving in the way it was designed – please read up on datepart boundaries in the online documentation for the function (link).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 18, 2018 at 9:30 am
John Mitchell-245523 - Wednesday, April 18, 2018 9:17 AMYou haven't provided an example of a pair of dates for which the function doesn't work, so I'll guess what the problem is. The way DATEDIFF works is by counting the number of boundaries crossed between two date or datetime values. So two dates four days apart may be interpreted as being 0 weeks or 1 week apart, depending on whether the period between them includes your local DATEFIRST day. The best way round this would be to calculate days directly after months, then divide by 7 to get the number of weeks.John
Thanks. The months between 2016-03-22 15:49:42.000 and 2016-05-18 10:03:20.000 is given as 2 months using the function. So the same problem. I will read more on DateDiff Thanks. Perhaps get the number of days and writing my own function.
April 18, 2018 at 9:36 am
Yes - same issue. You're crossing two month boundaries: March/April and April/May. You probably need extra logic in your function to check whether the date part is less for the end date than the start date, and if it is, subtract 1 from the value returned by DATEDIFF. I tell you what, though - I bet someone has done this before and written about it. Have you tried searching for it?
John
April 18, 2018 at 9:36 am
You say it isn't returning what you want, what do you want it to return. For two dates say '20170101 10:34:000' and '20180418 13:34:000' what should be returned?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2018 at 9:42 am
Sean Lange - Wednesday, April 18, 2018 9:36 AMYou say it isn't returning what you want, what do you want it to return. For two dates say '20170101 10:34:000' and '20180418 13:34:000' what should be returned?
For two dates say '20170101 10:34:000' and '20180418 13:34:000' I want the following to be returned: 1 Year, 3 months, 2 weeks, 3 days
April 18, 2018 at 9:43 am
John Mitchell-245523 - Wednesday, April 18, 2018 9:36 AMYes - same issue. You're crossing two month boundaries: March/April and April/May. You probably need extra logic in your function to check whether the date part is less for the end date than the start date, and if it is, subtract 1 from the value returned by DATEDIFF. I tell you what, though - I bet someone has done this before and written about it. Have you tried searching for it?John
Yes I did search. Could not find anything. Will keep on searching.
April 18, 2018 at 9:57 am
See if this gives you what you need -- it should at least be very close. Btw, T-SQL isn't like C#, you don't need separate statements for every calc.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER FUNCTION [dbo].[DateRangeText]
(
@FromDate DATETIME,
@ToDate DATETIME
)
RETURNS VARCHAR(50)
AS
BEGIN
RETURN (
SELECT
STUFF(
CASE WHEN YearsDiff = 0 THEN '' ELSE ' ' + CAST(YearsDiff AS varchar(4)) +
' Year' + CASE WHEN YearsDiff > 1 THEN 's' ELSE '' END END +
CASE WHEN MonthsDiff = 0 THEN '' ELSE ' ' + CAST(MonthsDiff AS varchar(2)) +
' Month' + CASE WHEN MonthsDiff > 1 THEN 's' ELSE '' END END +
CASE WHEN WeeksDiff = 0 THEN '' ELSE ' ' + CAST(WeeksDiff AS varchar(2)) +
' Week' + CASE WHEN WeeksDiff > 1 THEN 's' ELSE '' END END +
CASE WHEN DaysDiff = 0 THEN '' ELSE ' ' + CAST(DaysDiff AS varchar(2)) +
' Day' + CASE WHEN DaysDiff > 1 THEN 's' ELSE '' END END,
1, 1, '') AS Result
FROM (
SELECT DATEDIFF(YEAR, @FromDate, @ToDate) - CASE WHEN CONVERT(char(5), @FromDate, 1)
> CONVERT(char(5), @ToDate, 1) THEN 1 ELSE 0 END AS YearsDiff,
(DATEDIFF(MONTH, @FromDate, @ToDate) - CASE WHEN CONVERT(char(2), @FromDate, 3)
> CONVERT(char(2), @ToDate, 3) THEN 1 ELSE 0 END) % 12 AS MonthsDiff
) AS calc1
CROSS APPLY (
SELECT DATEDIFF(DAY, DATEADD(MONTH, MonthsDiff, DATEADD(YEAR, YearsDiff, @FromDate)), @ToDate) AS AllDaysDiff
) AS calc2
CROSS APPLY (
SELECT AllDaysDiff / 7 AS WeeksDiff, AllDaysDiff % 7 AS DaysDiff
) AS calc3)
END
GO
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply