forumadmin Absent Member.
Absent Member.

DateTime field error in Mashup Script

Question posted 11/16/10 by Bob Frinder

After upgrading from TeamTrack to SBM 2009 R3, I am getting an error on a date filed in the script.
I am trying to read a date field on the form and then trying to see if it exists in an Aux table. If it exists in the Aux table, we define this as being in a freeze period. However, when the Aux table is trying to be read with a ReadWithWhere(whereClause) I get the error.
th section of the script that is causing the error is here:
call ext.setCompatibilityVersion(7,0)
if (IssueType = 15 or IssueType = 16 or IssueType = 17) and (TransitionID = 4 or TransitionID = 28 or TransitionID = 88 or TransitionID = 144) or TransitionID = 119 or TransitionID = 106 then
fldOK = Shell.Item.GetFieldValue("Target Date" , intTargetDate)
shell.redomessage="we have a freeze date find :: " & "Target date is " & intTargetDate
tableId = "1015" ' Freeze Date
Set myRecord = Ext.CreateAppRecord(1015)
whereClause = "TS_FREEZE_DATE = " & intTargetDate
if myRecord.ReadWithWhere(whereClause) Then
fldOK = Shell.Item.SetFieldValue("Exception Required" , "1")
fldOK = myRecord.GetFieldValue("Freeze Date End", intFDEnd)
fldOK = Shell.Item.SetFieldValue("Exception Expiration Date" , intFDEnd)
fldOK = Shell.Item.SetFieldValue("Exception Required" , "0")
fldOK = Shell.Item.SetFieldValue("Exception Approved" , "0")
intFDEnd = ""
fldOK = Shell.Item.SetFieldValue("Exception Expiration Date" , intFDEnd)
end if
end if
The error message is below:
The record with the 'select U_FREEZE_DATES.TS_ID, U_FREEZE_DATES.TS_UUID, U_FREEZE_DATES.TS_TITLE, U_FREEZE_DATES.TS_FREEZE_DATE, U_FREEZE_DATES.TS_APP_FREEZE_TYPE, U_FREEZE_DATES.TS_FREEZE_DATE_END, U_FREEZE_DATES.TS_DESCRIPTION from U_FREEZE_DATES where TS_Freeze_Date = 2010-11-16T00:00:00+00:00' select statement could not be read in the 'Freeze Dates' database table.
Incorrect syntax near '00'.

Do I need to convert to Integer and query that?

1 Reply
forumadmin Absent Member.
Absent Member.

Re: DateTime field error in Mashup Script

Comment posted 11/19/10 by JeffMalin

Since the date changed from an integer to SQL Native Date, which basically works like a string in Where clauses, you need to surround it with single quotes:
whereClause = "TS_FREEZE_DATE = " & intTargetDate --> whereClause = "TS_FREEZE_DATE = '" & intTargetDate & "'"

The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.