SQLTeam.com | Weblogs | Forums

Calculating the number of days

tsql
sql2008r2

#1

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.

Please help on this.


#2

What about making datediff in hours and then divide by 24 h ?

SELECT DATEDIFF(HOUR,ED.documentModifiedOn,GETDATE())/24

#3

Don't think 24 hours will work either :frowning:

Test rig:

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
documentModifiedOn  NoOfDaysFileCheckOut NoOfHoursFileCheckOut 
------------------- -------------------- ------------------------ 
18July2017 23:59:59 2                    38
19July2017 00:00:00 1                    37
19July2017 13:02:01 1                    24
19July2017 13:02:02 1                    24
19July2017 13:02:03 1                    24
19July2017 23:59:59 1                    14

#4

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

#5

Thanks for the replies. I have modified the query as below and it is working fine.

select DATEDIFF(SECOND, ED.documentModifiedOn, GETDATE())/(60*60*24) AS [NoOfDaysFileCheckOut]
from ElectronicDocuments ED

The requirement is to show it as number of days alone and if the 24 hours is past after checking out, then we can show it as 1 day.


#6

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 ...


#7

@GeoJul,

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?