Date Calculation

[svm]

I have two Date variable Fields defined as Nvarchar in SQL database. What I want is to calculate the difference between the two datefields.

My first datefield in Table 1 is Project.ActSignDate and the second datefield in Table 2 is Project.DelDate.

In coding, how do you do this ?

 

[Naith]

You will want to use a combination of converting the date strings to dates with the CDate function, and then compare them with the DateDiff function.

DateDiff('d',CDate({Project.ActSignDate}),CDate({Project.DelDate}))

Verify the syntax in your client. I'm without Crystal Reports at the moment.

Naith

 

[synapsevampire]

The format of the nvarchar fields is key, you may have to convert them with more than just a CDate function.

I would use a SQL Expression to do both the conversion of the nvarchar to date, and perform the datediff function, obtaining a precalculated datediff value in the dataset.

As Naith referenced, this is dependent upon the type of SQL database, presumably you meant SQL Server.

-k

 

[mmorala]

This is what you could do to convert the string in your fields to dates.

StringVar tDate := {Project.ActSignDate} [x to y];
cdate(tDate);

You would have two sets of these, one for each date field. Make x and y equal to the first and last character position in your date field. For example, if your field is populated like this: 05/21/2002, then x = 1 and y = 10.

Then you could do a datediff function based on the two function fields.

Michael