Calculate Date Difference in session variable

[stratcat900]

I'm using the following to try and establish the number of days difference between dates from two different record sets. The result using test data should be 15, but I get 0. No code error message, but no accurate result either. I've checked the two date values involved, and they're returning the proper data in the proper format...just can't get the DateDiff function to work in the session variable...any help greatly apprciated.

Code:

<%Session("Lead")= DateDiff(, [rsSurvey.Fields.Item("tblShipDat").Value], [rsOrder.Fields.Item("tblValDat").Value])%>

[Newsgroup_User]

On 17 Sep 2006 in macromedia.dreamweaver.appdev, stratcat900 wrote:

> I'm using the following to try and establish the number of days
> difference between dates from two different record sets. The result
> using test data should be 15, but I get 0. No code error message,
> but no accurate result either. I've checked the two date values
> involved, and they're returning the proper data in the proper
> format...just can't get the DateDiff function to work in the session
> variable...any help greatly apprciated.
>
> Code:
>
> <%Session("Lead")= DateDiff(,
> [rsSurvey.Fields.Item("tblShipDat").Value],
> [rsOrder.Fields.Item("tblValDat").Value])%>

<%Session("Lead")= DateDiff(,

The interval needs to be a string:

<%Session("Lead")= DateDiff("d",

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/email.php

[stratcat900]

Thanks for the reply, Joe.

Sorry, I started out with the interval as a string, but got the following error message when the page is served:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/samples/admin/survey_detail.asp, line 224

rsLeadTime.Source = "SELECT DateDiff("d", [tblShipDat], [tblShipActualDat]) AS LeadTime FROM dbo.SampleCRM WHERE tblSampleID = " + Replace(rsLeadTime__MMColParam, "'", "''") + ""
--------------------------------------^

pointing at the the interval in quotations...that's why I tried the bracket.

Any additional suggestions for a resolution greatly appreciated.

Regards,

Chris Robinson

[stratcat900]

Sorry Joe..

My previous reply gave you the result from altering the code as you suggested, but as part of a recordset query as opposed to the session variable SQL...

Modified the SQL for the session variable as you suggested, and again, no server error, but the result is "0" when it should be "15". Any suggestions greatly apprciated.

<%Session("Lead")= DateDiff("d", [rsSurvey.Fields.Item("tblShipDat").Value], [rsOrder.Fields.Item("tblValDat").Value])%>

[Newsgroup_User]

You're showing two different things. One is VBScript. If you're using
VBScript, it should look like this:
<%
Session("Lead") = DateDiff("d", rsSurvey.Fields.Item("tblShipDat").Value,
rsOrder.Fields.Item("tblValDat").Value)
%>

The other you showed was T-SQL. If you're using T-SQL, it should look like
this (without the line wrap):
rsLeadTime.Source = "SELECT DATEDIFF(DAY, [tblShipDat], [tblShipActualDat])
AS LeadTime FROM dbo.SampleCRM WHERE tblSampleID = " +
Replace(rsLeadTime__MMColParam, "'", "''") + ""

You can use D in place of DAY, but no brackets, quotes, or anything else
around it. That's a keyword, not an object name or identifier.




"stratcat900" <webforumsuser@macromedia.com> wrote in message
news:eem5e9$qm$1@forums.macromedia.com...
> Sorry Joe..
>
> My previous reply gave you the result from altering the code as you
> suggested,
> but as part of a recordset query as opposed to the session variable SQL...
>
> Modified the SQL for the session variable as you suggested, and again, no
> server error, but the result is "0" when it should be "15". Any
> suggestions
> greatly apprciated.
>
> <%Session("Lead")= DateDiff("d",
> [rsSurvey.Fields.Item("tblShipDat").Value],
> [rsOrder.Fields.Item("tblValDat").Value])%>
>

[stratcat900 · Accepted solution]

Thanks very much for all the assistance...it was indeed the brackets around the table column names that crossed me up. Corrrect solution:

<%
Session("Lead") = DateDiff("d", rsSurvey.Fields.Item("tblShipDat").Value,
rsOrder.Fields.Item("tblValDat").Value)
%>

All the input is greatly appreciated. Really helps the non-professional coder make progress....

Regards,

Chris Robinson

[Newsgroup_User]

Use single quotes here instead:

DateDiff("d"

Like this:

DateDiff('d'

HTH
Cheers,
Rob
http://robgt.com/ [Tutorials and Extensions]
Firebox stuff: http://robgt.com/firebox
Skype stuff: http://robgt.com/skype
SatNav stuff: http://robgt.com/satnav

[Newsgroup_User]

Use single quotes here instead:

DateDiff("d"

Like this:

DateDiff('d'

HTH
Cheers,
Rob
http://robgt.com/ [Tutorials and Extensions]
Firebox stuff: http://robgt.com/firebox
Skype stuff: http://robgt.com/skype
SatNav stuff: http://robgt.com/satnav