 |
|
8000XXXX Errors Alerts ASP.NET 2.0
Classic ASP 1.0 COM / ActiveX Components Forms General Topics Date/Time Routines Email Scripts & Info Files/Directories & FSO Databases General Concepts Search Engine Optimization (SEO)Search
ASP FAQ Tutorials :: Classic ASP 1.0 :: Date/Time Routines :: How do I convert a DATEDIFF to days, hours, and minutes?
How do I convert a DATEDIFF to days, hours, and minutes?
Sometimes we want a delta to be shown this way:
This isn't a very easy format to derive from traditional DATEDIFF calculations. I suppose you could calculate all three deltas individually, and take the mod of the hours and minutes to determine the remainders. However, it might seem smarter to go straight to the lowest common denominator (minutes), and work up from there.
T-SQL
In T-SQL, we can use the following function:
Then we can call it like this:
VBScript
VBScript's equivalent looks remarkably similar:
And we would call it like this:
| Last updated 2 days, 3 hours, and 8 minutes ago |
This isn't a very easy format to derive from traditional DATEDIFF calculations. I suppose you could calculate all three deltas individually, and take the mod of the hours and minutes to determine the remainders. However, it might seem smarter to go straight to the lowest common denominator (minutes), and work up from there.
T-SQL
In T-SQL, we can use the following function:
| CREATE FUNCTION dbo.datediffToWords ( @d1 DATETIME, @d2 DATETIME ) RETURNS VARCHAR(255) AS BEGIN DECLARE @minutes INT, @word VARCHAR(255) SET @minutes = ABS(DATEDIFF(MINUTE, @d1, @d2)) IF @minutes = 0 SET @word = '0 minutes.' ELSE BEGIN SET @word = '' IF @minutes >= (24*60) SET @word = @word + RTRIM(@minutes/(24*60))+' day(s), ' SET @minutes = @minutes % (24*60) IF @minutes >= 60 SET @word = @word + RTRIM(@minutes/60)+' hour(s), ' SET @minutes = @minutes % 60 SET @word = @word + RTRIM(@minutes)+' minute(s).' END RETURN @word END GO |
Then we can call it like this:
| SELECT dbo.datediffToWords('2003-11-24 4:43 AM', '2003-12-01 7:45 PM') |
VBScript
VBScript's equivalent looks remarkably similar:
| <% function datediffToWords(d1, d2) minutes = abs(datediff("n", d1, d2)) if minutes <= 0 then word = "0 minutes." else word = "" if minutes >= 24*60 then word = word & _ minutes\(24*60) & " day(s), " end if minutes = minutes mod (24*60) if minutes >= 60 then word = word & minutes\(60) & " hours(s), " end if minutes = minutes mod 60 word = word & minutes & " minute(s)." end if datediffToWords = word end function %> |
And we would call it like this:
| <% response.write datediffToWords("2003-11-24 4:43 AM",_ "2003-12-01 7:45 PM") %> |
Related Articles
Can I get millisecond accuracy in ASP? Can I make VBScript format dates for me? Could I get a little help with dates? Given a date, how do I find the beginning and end of that week? Given two dates, how do I determine an age? How do I calculate dates, such as the first day of the month? How do I convert a timespan, in seconds, to HH:MM:SS? How do I convert local time to UTC (GMT) time? How do I count the number of business days between two dates? How do I delimit/format dates for database entry? How do I determine the number of seconds since 1/1/1970? How do I display time in military format? How do I implement a calendar / datepicker in ASP? How do I select time only from a DATETIME column? Why do I have problems inserting NOW() into a database? Why does JavaScript's document.lastModified() not work in ASP files?