| Author |
 Topic  |
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
 Posted - 03/20/2007 : 09:02:15
|
Excellent Post, Peter. The graphical example really makes it simple to understand the whole concept. I think newbies in datetime handling will find it much useful.
Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 03/20/2007 : 09:25:10
|
They have to read it first! 
That's the first challenge...
Thanks.
Peter Larsson
Helsingborg, Sweden |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 03/20/2007 : 17:39:24
|
More kinky stuff about DATEDIFF function
SELECT CURRENT_TIMESTAMP AS Now,
Status,
DATEADD(MONTH, DATEDIFF(MONTH, StartingPoint1, CURRENT_TIMESTAMP), StartingPoint2) AS [MONTH],
DATEADD(QUARTER, DATEDIFF(QUARTER, StartingPoint1, CURRENT_TIMESTAMP), StartingPoint2) AS [QUARTER],
DATEADD(YEAR, DATEDIFF(YEAR, StartingPoint1, CURRENT_TIMESTAMP), StartingPoint2) AS [YEAR]
FROM (
SELECT 'End Previous' AS Status, 0 AS StartingPoint1, -1 AS StartingPoint2 UNION ALL
SELECT 'Start Current', 0, 0 UNION ALL
SELECT 'End Current', -1, -1 UNION ALL
SELECT 'Start Next', -1, 0
) AS x
ORDER BY 5
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 03/21/2007 02:07:12 |
|
|
|
kbearhome
Starting Member
USA
36 Posts |
Posted - 03/23/2007 : 11:23:56
|
| This was an excellent way of breaking down the calculation to its simplest explanation. Thank you. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 03/24/2007 : 09:52:36
|
You're welcome and thanks.
Peter Larsson
Helsingborg, Sweden |
|
|
|
drewex
Starting Member
USA
4 Posts |
Posted - 08/08/2008 : 13:20:24
|
Slight change in your code, added negatif returns instead null
CREATE FUNCTION [dbo].[fnMonthsApart]
(
@FromDate DATETIME,
@ToDate DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @temp datetime
DECLARE @int int
IF @FromDate > @ToDate
BEGIN
SET @int = CASE
WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1
ELSE DATEDIFF(month, @FromDate, @ToDate)
END
END
ELSE
BEGIN
SET @temp = @ToDate
SET @ToDate = @FromDate
SET @FromDate = @temp
SET @int = -1 * (CASE
WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1
ELSE DATEDIFF(month, @FromDate, @ToDate)
END)
END
RETURN @int
END |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/08/2008 : 17:47:10
|
Why the bloated code? All it takes is thisCREATE FUNCTION dbo.fnMonthsApart
(
@FromDate DATETIME,
@ToDate DATETIME
)
RETURNS INT
AS
BEGIN
RETURN CASE
WHEN @FromDate > @ToDate AND DATEPART(day, @ToDate) > DATEPART(day, @FromDate) THEN +1
WHEN @FromDate <= @ToDate AND DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN -1
ELSE 0
END + DATEDIFF(month, @FromDate, @ToDate)
ENDAnd thisCREATE FUNCTION dbo.fnYearsApart
(
@FromDate DATETIME,
@ToDate DATETIME
)
RETURNS INT
AS
BEGIN
RETURN (CASE
WHEN @FromDate > @ToDate AND DATEPART(day, @ToDate) > DATEPART(day, @FromDate) THEN +1
WHEN @FromDate <= @ToDate AND DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN -1
ELSE 0
END + DATEDIFF(month, @FromDate, @ToDate)) / 12
END
E 12°55'05.25"
N 56°04'39.16" |
Edited by - SwePeso on 08/08/2008 17:51:47 |
|
|
|
Krull
Starting Member
USA
6 Posts |
Posted - 01/21/2009 : 13:55:11
|
I have to say this was a very helpful article, I could not figure out why my calculations were wrong sometimes and this almost solved my problem.
I've spent a few hours trying to adjust this to return results for my particular issue. I need to include all days in the ToDay month up to the same day of week -1 from FromDate.
Example: 3/1/2009 is a Sunday, my EndDate is 4/4/2009, as Saturday. This is over a month but for my purpose it would not be considered a month until 4/5/2009 (The 1st Sunday after EndDate)
I've tried all kinds of calculations using DatePart, DayOfWeek and so on but with no luck. Any help would be greatly appreciated.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/21/2009 : 16:20:21
|
Using the function fnMonthsApart in the article gives you the correct resultDECLARE @FromDate DATETIME,
@ToDate DATETIME
SELECT @FromDate = '2009-03-01', -- March 1st 2009
@ToDate = '2009-04-04' -- April 4th 2009
SELECT CASE
WHEN @FromDate > @ToDate THEN NULL
WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1
ELSE DATEDIFF(month, @FromDate, @ToDate)
END Result is 1.
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/21/2009 : 16:26:16
|
DECLARE @FromDate DATETIME,
@ToDate DATETIME
SELECT @FromDate = '2009-03-01', -- March 1st 2009, sunday
@ToDate = '2009-04-04' -- April 4th 2009, saturday
-- To deal with this particular problem, get following sunday if not already sunday for @ToDate
SET @ToDate = DATEADD(DAY, DATEDIFF(DAY, '1753-01-01', @ToDate) / 7 * 7, '1753-01-07')
-- Display result
SELECT @FromDate AS OriginalFromDate,
@ToDate AS NewToDate
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
Krull
Starting Member
USA
6 Posts |
Posted - 01/22/2009 : 08:40:44
|
quote:
Originally posted by Peso
Using the function fnMonthsApart in the article gives you the correct resultDECLARE @FromDate DATETIME,
@ToDate DATETIME
SELECT @FromDate = '2009-03-01', -- March 1st 2009
@ToDate = '2009-04-04' -- April 4th 2009
SELECT CASE
WHEN @FromDate > @ToDate THEN NULL
WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1
ELSE DATEDIFF(month, @FromDate, @ToDate)
END Result is 1.
E 12°55'05.63"
N 56°04'39.26"
I agree it is a correct result, however for my needs it should return 0 unless its that next sunday(4/5/2009). I've been trying to adjust the enddate so as to trick the function but I think it would be best to actually have the function do the adjustment so it could be re-used in some other projects.
I hope that makes sence. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/22/2009 : 08:45:58
|
Give me some more sample data ranges and expected "month" count.
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
Krull
Starting Member
USA
6 Posts |
Posted - 01/22/2009 : 09:10:37
|
Start 1/3/2010
End 1/6/2010
Returns 0
Start 1/3/2010
end 3/6/2010
Returns 1
Start 1/3/2010
End 3/7/2010
Returns 2
Start 6/6/2010
End 7/1 thru 3/2010
Returns 0
Start 6/6/2010
End 7/4/2010
Returns 1
Start 1/3/2010
End 12/4/2010
Returns 10
Start 1/3/2010
End 12/5/2010
Returns 11 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/22/2009 : 09:21:26
|
Start 6/6/2010
End 7/1 thru 3/2010 -- Should be 7 to 10 July
Returns 0
Start 6/6/2010
End 7/4/2010 -- Should be 11 July
Returns 1
E 12°55'05.63"
N 56°04'39.26" |
Edited by - SwePeso on 01/22/2009 09:40:29 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/22/2009 : 09:37:19
|
This should do it if I understand you correctlyDECLARE @FromDate DATETIME,
@ToDate DATETIME
SELECT @FromDate = '2009-03-01',
@ToDate = '2009-04-04'
-- Display the month count according to business rules, not calendar
SELECT MAX(mc) AS MonthCount
FROM (
SELECT 0 AS mc,
@FromDate AS dt
UNION ALL
SELECT DATEDIFF(MONTH, @FromDate, @ToDate) - 1,
DATEADD(DAY, DATEDIFF(DAY, @FromDate, DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate) - 1, @FromDate)) / 7 * 7 + 7, @FromDate)
UNION ALL
SELECT DATEDIFF(MONTH, @FromDate, @ToDate),
DATEADD(DAY, DATEDIFF(DAY, @FromDate, DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate), @FromDate)) / 7 * 7 + 7, @FromDate)
) AS d
WHERE @ToDate >= dt
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
Krull
Starting Member
USA
6 Posts |
Posted - 01/22/2009 : 09:55:09
|
Actually the 1st date would be correct.
Start 6/6/2010 - 1st Sunday
End 7/1 thru 3/2010 -- 1st Week 7/4/2010 - 1st Sunday Would return 1
Returns 0
It looks like the problem starts when the StartDate DayOfMonth(6) is higher than the EndDate DayOfMonth(3). July 11th would be the 2nd Sunday, July 4th would be the 1st Sunday as the Start Day of June 6th 2010 is the 1st Sunday as well.
I apoligize for the confusion.
|
|
|
|
Krull
Starting Member
USA
6 Posts |
Posted - 01/22/2009 : 11:38:39
|
Does this look reasonable, minor adjustment? I ran it though a bunch of dates and had positive results.
DECLARE @FromDate DATETIME,
@ToDate DATETIME
DECLARE @Calculation int
SELECT @FromDate = '2010-6-6',
@ToDate = '2010-7-4'
IF Day(@FromDate) >= Day(@ToDate)
SET @Calculation = 0
ELSE
SET @Calculation = 7
-- Display the month count according to business rules, not calendar
SELECT MAX(mc) AS MonthCount
FROM (
SELECT 0 AS mc,
@FromDate AS dt
UNION ALL
SELECT DATEDIFF(MONTH, @FromDate, @ToDate) - 1,
DATEADD(DAY, DATEDIFF(DAY, @FromDate, DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate) - 1, @FromDate)) / 7 * 7 + @Calculation , @FromDate)
UNION ALL
SELECT DATEDIFF(MONTH, @FromDate, @ToDate),
DATEADD(DAY, DATEDIFF(DAY, @FromDate, DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate), @FromDate)) / 7 * 7 + @Calculation, @FromDate)
) AS d
WHERE @ToDate >= dt |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/22/2009 : 13:31:09
|
As long as it work, use it.
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/22/2009 : 13:41:49
|
DECLARE @FromDate DATETIME,
@ToDate DATETIME
SELECT @FromDate = '2010-6-6',
@ToDate = '2010-7-4'
-- Display the month count according to business rules, not calendar
SELECT MAX(mc) AS MonthCount
FROM (
SELECT 0 AS mc,
@FromDate AS dt
UNION ALL
SELECT DATEDIFF(MONTH, @FromDate, @ToDate) - 1,
DATEADD(DAY, DATEDIFF(DAY, @FromDate, DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate) - 1, @FromDate)) / 7 * 7, @FromDate)
WHERE Day(@FromDate) >= Day(@ToDate)
UNION ALL
SELECT DATEDIFF(MONTH, @FromDate, @ToDate) - 1,
DATEADD(DAY, DATEDIFF(DAY, @FromDate, DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate) - 1, @FromDate)) / 7 * 7 + 7, @FromDate)
WHERE Day(@FromDate) < Day(@ToDate)
UNION ALL
SELECT DATEDIFF(MONTH, @FromDate, @ToDate),
DATEADD(DAY, DATEDIFF(DAY, @FromDate, DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate), @FromDate)) / 7 * 7, @FromDate)
WHERE Day(@FromDate) >= Day(@ToDate)
UNION ALL
SELECT DATEDIFF(MONTH, @FromDate, @ToDate),
DATEADD(DAY, DATEDIFF(DAY, @FromDate, DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate), @FromDate)) / 7 * 7 + 7, @FromDate)
WHERE Day(@FromDate) < Day(@ToDate)
) AS d
WHERE @ToDate >= dt
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
Krull
Starting Member
USA
6 Posts |
Posted - 01/23/2009 : 08:34:16
|
| Thanks Peso! This looks like it's working perfectly. |
|
|
|
Topic |
|
|
|
Article: DATEDIFF Function Demystified
