I just discover the result for my query that had use the function dateDiff seems to be in-correct, no matter what's the date, the dateDiff always return a zero. Am I using it in-correctly?
select DateDiff(mm,11/1/2004, 12/31/2005)
SELECT component_id, component_description,
SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 2 THEN component_qty ELSE 0 END) AS mm1,
SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 1 THEN component_qty ELSE 0 END) AS mm2,
SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 0 THEN component_qty ELSE 0 END) AS mm3,
sum(component_qty) as total
FROM view_jobcomponent
WHERE date_complete between '10/1/2004' and '12/31/2004'
GROUP BY component_id, component_description
order by component_id, component_description
select DateDiff(mm,11/1/2004, 12/31/2005) should return 0.
11/1/2004 isn't a date, it's the number 11 divided by 1 divided by 2004 which is about 15 minutes into the very first day ever (ok, more like 1/1/1900 00:15:00). and 12/31/2005 is like 15 minutes and a few seconds into the very first day, and there is no month difference between them.
select DateDiff(mm,'11/1/2004', '12/31/2005') however will give you 13.
No comments:
Post a Comment