AnsweredAssumed Answered

Model Builder date variable problem

Question asked by cmatthews on Jan 7, 2014
Latest reply on Jan 9, 2014 by cmatthews
I've created a model which connects to a SQL database and brings in a table using the Make Query tool. I'm using the Calculate Value tool to create the Expression used on the Make Query Table tool.

The Calculate Value tool is consuming 2 model parameter variables; Start_Date which is a string and dblYears which is a double.

The user is expected to provide the date as ddmmyyyy, the Calculate Value tool reformats this when creating the Expression.

When the date string is received by the Calculate Value tool it seems to handle it as a different data type depending on the value i.e. 26052006 is treated as a string and outputs an expression that is as required; 01052006 seems to be treated as a integer and is mangled to 283654!!! I don't understand how it gets to that value.

Here are the details of my Calculate Value tool;

Expression:
x(str(%Date_String%),str(%dblYears%))

Code Block:
import string
def x(Start_Date,dblYears):
   # The If loop construction is temporary to show what values are being received in the function (debugging)...
   # Start_Date should always have 8 characters
   # Dates with a leading zero are getting manged into a 6 figure values
   # We can see the date value in the Expression displayed in the processing window.

   if (len(Start_Date) < 8):
      strWhere = "qryELM.elmPK = tblELM.elmPK AND dbo.tblELM.Remarks = '< 8 " + Start_Date + "' AND dbo.tblELM.EnquiryFK IN (4,22,25,61)"
   elif (len(Start_Date) == 8):
      strDay = Start_Date[:2]
      strMonth = Start_Date[2:-4]
      strFYear = Start_Date[-4:]
      strFromDate = "'" + strFYear + "-" + strDay + "-" + strMonth + "'"

      strWhere = "qryELM.elmPK = tblELM.elmPK AND dbo.tblELM.LoggedDate >= " + strFromDate + " AND dbo.tblELM.LoggedDate <= DATEADD(year, " + dblYears + "," + strFromDate + ") AND dbo.tblELM.EnquiryFK IN (4,22,25,61)"
   elif (len(Start_Date) > 8):
      strWhere = "qryELM.elmPK = tblELM.elmPK AND dbo.tblELM.Remarks = '> 8 " + Start_Date + "' AND dbo.tblELM.EnquiryFK IN (4,22,25,61)"

   return strWhere



I've been beating my head against a wall on this one for a few days now. Any help would be greatly received.

Outcomes