DateDiff - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DateDiff

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-09-07 : 11:24:15

Hi guys,

I'm having a bit of trouble with a DateDiff function and I'm hoping you folks might be able to shed some light on the problem here's the code i'm trying to run:
DECLARE
@FromDate DATETIME,
@ToDate DATETIME

SET @FromDate ='2010-06-01 09:00:00'
SET @ToDate ='2010-06-01 10:00:00'

SELECT T0.ObjectID,
T0.POSite AS Site,
T0.PO AS PONumber,
T0.Buyer AS Buyer,
T1.Description AS TradingPartner,
T3.OrdResc AS ResourceCode,
T3.ItemDesc AS ResourceDescription,
T3.OrdQty AS QuantityOrdered,
T3.OrdQtyUM AS UOM,
T3.RecQty AS QuantityReceived,
T3.ApprovDt AS ApprovalDate,
T3.LastRecDt AS ReceiptDate,
T4.CatCodesCode1

FROM poPurchaseOrder T0 (NOLOCK)
INNER JOIN poLine T2 (NOLOCK) ON T2.ParentObjectID = T0.ObjectID
AND T2.ParentClassID = 11588
AND T2.CollectionID = 1
INNER JOIN poDelivery T3 (NOLOCK) ON T3.ParentObjectID = T2.ObjectID
AND T3.ParentClassID = 11608
AND T3.CollectionID = 1
LEFT OUTER JOIN fdTradingPartne T1 (NOLOCK) ON T1.ObjectID = T0.OrderFrTPObjectID
LEFT OUTER JOIN fdBasResc T4 (NOLOCK) ON T4.ObjectID = T2.OrdRescObjectID

WHERE T3.ApprovDt BETWEEN @FromDate AND @ToDate
AND DATEDIFF(hh, T3.LastRecDt, T3.ApprovDt)< 4

the results i'm getting from this query are displaying rows where the date range is a few days old. Does the DATEDIFF function not calculate the days and just look at the timestamp?

Thank you for any help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-07 : 11:56:04

Take your datecolumns into the select list and see if LastRecDt is greater than ApprovDt.
Maybe that is the reason?

Can you post examples for the unexpected output?

No, you're never too old to Yak'n'Roll if you're too young to die.

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-09-08 : 06:50:59

thanks for taking the time to look at my problem webfred, turns out I was using an incorrect date field, my query i now working!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources