I have a table called ElectronicDocuments with the columns DocumentName, documentModifiedOn.
I have to find the number of days the document is checked out for editing.
I am calculating the same as below:
select DATEDIFF(DAY, ED.documentModifiedOn, GETDATE()) AS [NoOfDaysFileCheckOut]
from ElectronicDocuments ED
But I am not sure whether it will show the exact count.
If the checked out time is 19July2017 13:02:02. It should show the count as 1, only when the GETDATE value is 20July2017 13:02:02.
SELECT documentModifiedOn
, DATEDIFF(DAY, documentModifiedOn, '20July2017 13:02:02') AS [NoOfDaysFileCheckOut]
, DATEDIFF(Hour, documentModifiedOn, '20July2017 13:02:02') AS [NoOfHoursFileCheckOut]
FROM
(
SELECT [documentModifiedOn] = '19July2017 13:02:02'
UNION ALL SELECT '19July2017 13:02:01'
UNION ALL SELECT '19July2017 13:02:03'
UNION ALL SELECT '19July2017 00:00:00'
UNION ALL SELECT '19July2017 23:59:59'
UNION ALL SELECT '18July2017 23:59:59'
) AS X
ORDER BY documentModifiedOn
You are right Kristen; I can check the time of the field and the time of getdate , and base on this , subtract 1.
SELECT documentModifiedOn
, DATEDIFF(DAY, documentModifiedOn, '20July2017 13:02:02') AS [NoOfDaysFileCheckOut]
, DATEDIFF(Hour, documentModifiedOn, '20July2017 13:02:02') AS [NoOfHoursFileCheckOut]
, CASE WHEN CONVERT(time,documentModifiedOn,108) > CONVERT(time,'20July2017 13:02:02',108) THEN DATEDIFF(DAY,documentModifiedOn,'20July2017 13:02:02') - 1
ELSE DATEDIFF(DAY,documentModifiedOn,'20July2017 13:02:02')
END newNoOfDaysFileCheckOut
FROM
(
SELECT [documentModifiedOn] = '19July2017 13:02:02'
UNION ALL SELECT '19July2017 13:02:01'
UNION ALL SELECT '19July2017 13:02:03'
UNION ALL SELECT '19July2017 00:00:00'
UNION ALL SELECT '19July2017 23:59:59'
UNION ALL SELECT '18July2017 23:59:59'
) AS X
ORDER BY documentModifiedOn
I expect you will have all sorts of other problems if a document is checked out for "many years" but DATEDIFF(SECOND will give overflow error if above 68 years
I've had that happen in an "impossible" situation, because the underlying data was goofy ...
For Belt & Braces do a YEARS check first perhaps ...
You need to define what a "day" actually is. Are you using strictly whole calendar days with the understanding that something checked out one minute before midnight and then checked in at midnight will constitute a whole day?