-
I have a temp table that gives me two time entries we'll say invoice_date1 and invoice_date2
i need to extract the time difference between the two dates and if it is greater than an hour or 60 minutes i need to return it in a count statement.
how would i do this?
i did this and it is not working:
SELECT COUNT (*)
FROM #WorkingData
WHERE ledger_id IS NULL
AND ledger_id < 0
AND DATEDIFF ( mi , invoice_date1, invoice_date2) > 60
the only part that does not work in this is my date statment when i query before that statement I get correct results after that statement I do not.
-
Hey Bebe!
Assuming #WorkingData field structure is something like:
Field Name .......... Datatype
ledger_id .............. integer (or long, double, single, numeric, whatever)
invoice_date1 ....... datetime
invoice_date2 ....... datetime
You won't have many records where the ledger_id is both NULL and less than zero, so you should change that part of your SARG.
Then, assuming invoice_date1 is earlier than invoice_date2 (since you otherwise get a negative return), the next hurdle is to avoid calculating a DATEDIFF return for null dates or a range greater than an integer value.
See if something like this work.
Code:
SELECT COUNT (*)
FROM #WorkingData
WHERE ledger_id < 0
AND DATEDIFF ( hh
, ISNULL( invoice_date1, GETDATE() )
, ISNULL( invoice_date2
, ISNULL( invoice_date1, GETDATE() ))) > 1
Hope this helps.
-
Sorry i wrote it wrong it should be;
SELECT COUNT (*)
FROM #WorkingData
WHERE ledger_id2 IS NULL
AND ledger_id1 < 0
AND DATEDIFF ( mi , invoice_date1, invoice_date2) > 60
does this make a difference?
-
If ledger_id1, ledger_id2, invoice_date1, invoice_date2 are all the applicable the fields in your #WorkingData table, then try...
Code:
SELECT COUNT (*)
FROM #WorkingData
WHERE ledger_id2 IS NULL
AND ledger_id1 < 0
AND DATEDIFF ( hh
, ISNULL( invoice_date1, GETDATE() )
, ISNULL( invoice_date2
, ISNULL( invoice_date1, GETDATE() ))) > 1
Let me know how it works. ;)
