May 22, 2002 at 11:10 am
Hello, I'm new to sqlservercentral.com, but quickly finding how VALUABLE this site is!
OK, I'm trying to compare a product's commitment period to its actual commitment length. eg: a "12 month product" = 12, compare to 6/01/2001 to 5/31/2002 = 12.
Problem is, DATEDIFF(mm, 6/01/2002, 5/31/2001) gives 11 instead of 12!!! I've looked into using DATEADD but the result is a date, I can't use that to compare... How should I do this? Pls help! Thank you.
May 22, 2002 at 11:23 am
I get 13 when I run either of these:
select DATEDIFF(m, '5/31/2001', '6/01/2002' )
select DATEDIFF(m, '6/01/2002', '5/31/2001' )
Andy
May 22, 2002 at 11:33 am
Try this:
select DATEDIFF(m, '6/01/2001', '5/31/2002')
This is exactly how I wrote it in my query:
DATEDIFF(mm, od.order_start_date_hj, od.order_end_date_hj)
and the "order_start_date / order_end_date" are in this format: 2002-10-31 00:00:00.000 and I'm using SQL Server 7.
I think that SQL Server only count the months 6 to 5 = 11 but ignore it is actually 6/01 to 5/31... how can I get it right??
Thank you.
May 22, 2002 at 11:47 am
You're right, that returns 11. What was I doing? Ah well. Best you can do is add one. When you use 5/31 you're really using 5/31/02 midnight. It wont return 12 until midnight on 6/1/02.
Andy
May 22, 2002 at 1:26 pm
Just DATEADD(dd, 1, end_date) and it works... Thanks Andy!
It seems kinda silly to have to do this, I wonder if this is something that the SQL team will improve for the next release of SQL Server.
May 22, 2002 at 4:38 pm
I think you'd have to convince them that its wrong to start with.
Andy
June 3, 2002 at 11:37 am
New follow up question:
Found another problem, adding 1 day to the end dates fixed the problem of DATEDIFF(mm, 1-01-2001, 12-31-2002) = 11.
But this also created a new problem, for example a 6 months product that starts on 12/31/2001 and ends on 6/30/2002. 6/30 + 1day = 7/1/2002, and DATEDIFF(mm, 12-31-2001, 7-01-2002) = 7 months!
I'm thinking I need to specify that we only need to add 1 day to the end date if the start date is the first day of the month. How can I do this? Or is there any other way to fix this problem?
June 3, 2002 at 8:13 pm
The problem with this is how do you decide which way to slide. You have to lay a set of ground rules to say when to roll up or down before you can build a proper query.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 4, 2002 at 1:59 am
try this
declare @datStart datetime,
@datEnd datetime,
@datCheck datetime,
@intMonth integer
set @datStart='01/01/2001'
set @datEnd = '12/31/2002'
set @intMonth = (select DATEDIFF(m, @datStart, @datEnd))
select @intmonth
set @datCheck= dateadd(m,-@intMonth,@datEnd)
select @datCheck
if @datCheck > @datStart
set @intMonth = @intMonth + 1
select @intMonth
June 4, 2002 at 3:25 pm
Thanks to both of you. It's true, I've got to check with the business logic team to see exactly when I should roll up/down a month.
Klass-Jan, I've implemented your script into mine using a CASE statement:
SELECT bla,
bla,
bla,
CASE WHEN
DATEADD(m, -1*DATEDIFF(m,order_start_date_hj,order_end_date_hj),order_end_date_hj) > order_start_date_hj
THEN
DATEDIFF(m,order_start_date_hj,order_end_date_hj) + 1
ELSE
DATEDIFF(m,od.order_start_date_hj,od.order_end_date_hj)
END AS 'CalculatedCommit'
FROM blabla
WHERE
'CommitmentChosen' <> 'CalculatedCommit'
Looks very untidy, I wanted to use the variables @intMonth, @datStart but I just can't get them tidy AND working 😛 Well, big thanks to you.
PS. I feel sorry that Neitherlands didn't make it to the World Cup this year. They're always my favorite!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply