Model Builder date variable problem

948
8
Jump to solution
01-07-2014 05:58 AM
ChrisMatthews
New Contributor III
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.
0 Kudos
1 Solution

Accepted Solutions
ChrisMatthews
New Contributor III
Wayne

Many thanks for all your input and suggestions. I've finally resolved this issue...


I've updated my Expression on the Calculate Value tool and added double quotes around the Start_Date variable, as follows;

x(str("%Start_Date%"),str(%dblYears%))


From my experience in other programming languages I would assume that this would that this would create a string variable equal to the name of the object in the quotes, but not in this case!

View solution in original post

0 Kudos
8 Replies
T__WayneWhitley
Frequent Contributor
??? DATEADD(year, " + dblYears + "," + strFromDate + ")

That's in your where clause and I think that should be SQL, so I'm not sure that makes sense.  Shouldn't it be similar to VBScript?, where the syntax is the following where the interval is essentially day, month, year, etc and the number is a long integer val, and date is a literal?:
DateAdd(interval, number, date)

This may help:
http://www.pressthered.com/adding_dates_and_times_in_python/
0 Kudos
ChrisMatthews
New Contributor III
Wayne

Thanks for taking a look.

I'm glad to say the DATEADD SQL is working fine. The issue I have is with the date string being parsed into the CALCULATE VALUE tool.

Please see the following link, you can see the syntax for DATEADD is correct: http://www.w3schools.com/sql/func_dateadd.asp


Regards

Chris



??? DATEADD(year, " + dblYears + "," + strFromDate + ")

That's in your where clause and I think that should be SQL, so I'm not sure that makes sense.  Shouldn't it be similar to VBScript?, where the syntax is the following where the interval is essentially day, month, year, etc and the number is a long integer val, and date is a literal?:
DateAdd(interval, number, date)

This may help:
http://www.pressthered.com/adding_dates_and_times_in_python/
0 Kudos
T__WayneWhitley
Frequent Contributor
I still don't get it -- bear with me for a moment... I may be wrong but this is worth taking a look at (and I still think your DATEADD statement is in error).  Can you fill me in on what the '+' symbols are referring to in your posted strWhere statement?-


DATEADD(year, " + dblYears + "," + strFromDate + ")

In the SQL doc page you refer to, I see the syntax given is:

DATEADD(datepart,number,date)

...where 'datepart' is what I refer to as a keyword (year, month, day, nanosecond, etc.), 'number' is an integer (pos or neg), and 'date' is a valid date data type.  I think those params have to be passed 'as-is' or should I say as the SQL db expects to receive them, is that right?  So what I'm not getting is you're parsing text for both of your latter 2 params...for example, your strFromDate is assembled from the in-line var substituted param passed into the function, str(%Date_String%), and is maintained as text:

strFromDate = "'" + strFYear + "-" + strDay + "-" + strMonth + "'"


The example given at the page you gave clearly states it must be "...date is a valid date expression", which means to me you can pass a function that must evaluate to a date type.

The example given there:
DATEADD(day,45,OrderDate)

Additionally, of some of the examples given at the source I suggested earlier, see the SQL syntax examples, such as this one (that returns a date via a function, getdate(), for the final param):
select DATEADD(day, 1, getdate())

However, if I'm wrong looks like you need to search/test your if-elif-elif branching (no else?) for where it is getting handled wrong...


Wayne
0 Kudos
ChrisMatthews
New Contributor III
I still don't get it -- bear with me for a moment... I may be wrong but this is worth taking a look at (and I still think your DATEADD statement is in error).  Can you fill me in on what the '+' symbols are referring to in your posted strWhere statement?-

DATEADD(year, " + dblYears + "," + strFromDate + ")


The + symbols are concatenating the various strings together.

I believe you are right and that you are wrong in barking up the DATEADD tree. The problem is that the Start_Date parameter is mangled whilst being parsed and therefore the strFromDate variable is not being created correctly and I end up with a non-valid date expressison.

To eliminate various factors I've rewritten the code in my Calculate Value tool and removed most of the un-necessary bit 'n pieces;

import string
def x(Start_Date,dblYears):

   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.EnquiryFK IN (4,22,25,61)"

   return strWhere


See the results below generated for two examples;

01012006 >
qryELM.elmPK = tblELM.elmPK AND dbo.tblELM.LoggedDate >= '7270-26-' AND dbo.tblELM.EnquiryFK IN (4,22,25,61)

31052001 >
qryELM.elmPK = tblELM.elmPK AND dbo.tblELM.LoggedDate >= '2001-31-05' AND dbo.tblELM.EnquiryFK IN (4,22,25,61)
0 Kudos
BryanDavisson
New Contributor
Oh, okay.  I didn't see that...and to make code more readable I try to use 'format' or substitution to avoid not only that concatenating confusion but to avoid having to use so many escape characters on certain things...

Anyway, your problem is how you're passing in the value to the function that should be a string, including the leading zero (0).

See this simple IDLE testing:
>>> x(str(01012006), str(0.0))
"qryELM.elmPK = tblELM.elmPK AND dbo.tblELM.LoggedDate >= '7270-26-' AND dbo.tblELM.EnquiryFK IN (4,22,25,61)"
>>> 

>>> str(01012006)
'267270'
>>> 


...so if you want to avoid that 'interpretation' you could pass in an actual date, or even a strft date.  Hope that helps.


-Wayne
0 Kudos
T__WayneWhitley
Frequent Contributor
Oh, okay. I didn't see that...and to make code more readable I try to use 'format' or substitution to avoid not only that concatenating confusion but to avoid having to use so many escape characters on certain things...

Anyway, your problem is how you're passing in the value to the function that should be a string, including the leading zero (0).

See this simple IDLE testing:
>>> x(str(01012006), str(0.0))
"qryELM.elmPK = tblELM.elmPK AND dbo.tblELM.LoggedDate >= '7270-26-' AND dbo.tblELM.EnquiryFK IN (4,22,25,61)"
>>> 

>>> str(01012006)
'267270'
>>>


...so if you want to avoid that 'interpretation' you could pass in an actual date, or even a strft date. Hope that helps.


-Wayne
0 Kudos
T__WayneWhitley
Frequent Contributor
This isn't the only way, but I believe a good way to handle this issue...I'll try to explain my approach backwards - probably make more sense that way:

If indeed you do need a string for the date in the SQL query, then I think you should replace the following line (and supporting lines of code in your def function) in order to make a more reliable one that can produce a correct date string (e.g., '2001-31-05').

strFromDate = "'" + strFYear + "-" + strDay + "-" + strMonth + "'"


Probably best to 'intercept' what is being passed into the function and replace it with a 'time object' - see this simplified version below (all of the following that I'm doing is tested via IDLE):
>>> import time

>>> def x(Start_Date):
 strFromDate = time.strftime("%Y-%d-%m", Start_Date)
 strWhere = "qryELM.elmPK = tblELM.elmPK AND dbo.tblELM.LoggedDate >= '" + strFromDate + "' AND dbo.tblELM.EnquiryFK IN (4,22,25,61)"
 return strWhere



The Start_Date param that is passed in is what I call the time value object (rather than a string)...consider the following where I'm preparing your 2 values with the time module's strptime (I suppose this part could be part of the function, but wanted to show passing into the function an object and keep it similar to your function):
>>> # Your 1st date as 3 variables, type integer:
>>> day, mon, year = 1, 1, 2001

>>> print day, mon, year
1 1 2006

>>> # converted to 'concatenated' space-delimited string:
>>> strDate = '{0} {1} {2}'.format(day, mon, year)

>>> print strDate
1 1 2006

>>> # the string converted to time.struct obj 
>>> objYourDate1 = time.strptime(strDate, '%d %m %Y')

>>> print objYourDate1
time.struct_time(tm_year=2006, tm_mon=1, tm_mday=1, tm_hour=0, tm_min=0, tm_sec=0, tm_wday=6, tm_yday=1, tm_isdst=-1)


>>> # Your 2nd date as 3 variables, integer, etc. etc. (no further comments necessary): 
>>> day, mon, year = 31, 5, 2001
>>> print day, mon, year
31 5 2001
 
>>> strDate = '{0} {1} {2}'.format(day, mon, year)

>>> print strDate
31 5 2001

>>> objYourDate2 = time.strptime(strDate, '%d %m %Y')

>>> print objYourDate2
time.struct_time(tm_year=2001, tm_mon=5, tm_mday=31, tm_hour=0, tm_min=0, tm_sec=0, tm_wday=3, tm_yday=151, tm_isdst=-1)



And finally, using the new def function by 'mining' or converting the objs with the alternate time module's strftime - which should always return the correctly formatted date string:
>>> x(objYourDate1)
"qryELM.elmPK = tblELM.elmPK AND dbo.tblELM.LoggedDate >= '2006-01-01' AND dbo.tblELM.EnquiryFK IN (4,22,25,61)"
 
>>> x(objYourDate2)
"qryELM.elmPK = tblELM.elmPK AND dbo.tblELM.LoggedDate >= '2001-31-05' AND dbo.tblELM.EnquiryFK IN (4,22,25,61)"


Hope that helps... if not clear, let me know.


Enjoy,
Wayne

...oh and by the way, this Python reference was extremely useful:
http://docs.python.org/2/library/time.html#time.strftime
0 Kudos
ChrisMatthews
New Contributor III
Wayne

Many thanks for all your input and suggestions. I've finally resolved this issue...


I've updated my Expression on the Calculate Value tool and added double quotes around the Start_Date variable, as follows;

x(str("%Start_Date%"),str(%dblYears%))


From my experience in other programming languages I would assume that this would that this would create a string variable equal to the name of the object in the quotes, but not in this case!
0 Kudos