2. ASPRunnerPro General questions
  3. Date difference
This topic is locked

Date difference

 		5/7/2009 08:07:26
ASPRunnerPro General questions
S
 srajan author

Hi.. I have been trying my hands on with ASP Runner to build a few applications and its fantastic. As I'm a newbie not too sure about the below.
I would like to calculate the no. of days between 2 fields and display it
DateOriginated : Date 1
DateCompleted : Date 2
TimeTaken : Date 2 - Date 1 (in no. of days)
I have tried the dict function but not too sure about the code.
Also I would like to know where to paste the code ( I'm using ASP RunnerPro5.2 )
Can someone help please.
A
 		agruspe 5/7/2009

Try the DateDifffunction to calculate the days between fields.
If you are using Access as the back-end, you can also create a query to create a calculated DateDiff field.
For more information on the DateDiff function, follow this link
S
 		srajan author 5/11/2009

I am using SQL server 2005 as backend and using ASP Runner 5.2
The field names are DateCreated and DateCompleted.
The code I tried was DateDiff(d,DateCreated,DateCompleted) as custom code for the field Time Taken
Could anyone help with the code that calculates the no. of days between Date Created and Date Completed and display it in the Time Taken field.
J
 		Jane 5/12/2009

I recommend you to check this article:

http://msdn.microsoft.com/en-us/library/ms...94(SQL.90).aspx
S
 		srajan author 5/12/2009

Thanks for your help Jane.
In the end this is what a friend of mine suggested and it worked just perfect !!
It would be best to do this at database level, make sure you are capturing datetime into the fields then create a NEW column called Time_Taken or something like that, then execute the following script against your database, this will create a function called [datediffToWords]
/** Object: UserDefinedFunction [dbo].[datediffToWords] Script Date: 05/12/2009 10:42:31 **/

SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON

GO
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 mins.'

ELSE

BEGIN
SET @word = ''

IF @minutes >= (24607)

SET @word = @word

  • RTRIM(@minutes/(24607))+' Week(s) '
    SET @minutes = @minutes % (24607)

    IF @minutes >= (24*60)

    SET @word = @word
  • RTRIM(@minutes/(2460))+' 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
    Go back into the field you created above and go to Table Designer and expand Computed Column Specification and enter the following:
    ([dbo].datedifftowords)
    This will do an auto calculation for you, then just add this new column to your ASPR project and just enable it for LIST and VIEWS only.