1

I am trying to calculate a field based on the date values in another field. Here is my code from the pre-logic script code box:

def getHalf(dfield):
  if dfield >= date '2017-08-27 00:00:00' AND dfield < date '2017-08-27 
  12:00:00':
    return 1

The field calculator fails on line 2. The input field is formatted as a date field, its values look like this when viewed in the attribute table:

8/27/2017 10:05:00 PM

However they look like this when I perform a select by attributes on the field, so I am pretty sure I got the syntax right:

date '2017-08-27 22:05:00'

And then of course this is the line of code attempting to calculate the field:

getHalf(!timestamp_cst!)
  • 1
    Your syntax is not correct - you've mixed SQL with Python. You need to use python's datetime to ensure you're treating them as dates – Midavalo Oct 23 '17 at 15:19
  • That's odd as when I use the following syntax in the select by attributes box, it works: timestamp_cst >= date '2017-08-28 00:00:00' AND timestamp_cst < date '2017-08-28 12:00:00' – blwoods Oct 23 '17 at 15:25
  • 1
    Select by Attributes uses SQL, your field calculator is using Python – Midavalo Oct 23 '17 at 15:27
2

You have mixed SQL syntax with Python syntax. The Select by Attributes uses SQL but the Field Calculator python parser uses python.

def getHalf(dfield):
    df = datetime.datetime.strptime(dfield, '%Y/%m/%d %H:%M:%S')
    d1 = datetime.datetime.strptime('2017-08-27 00:00:00', '%Y/%m/%d %H:%M:%S')
    d2 = datetime.datetime.strptime('2017-08-27 12:00:00', '%Y/%m/%d %H:%M:%S')
    if df >= d1 and df < d2:
        return 1

This may depend on how your dates are stored - mine are different to yours, so I've had to use the strptime() format differently to yours, and I have tried to adjust here based on the dates you've shown in your question. For more info on the formatting tags see Python - Basic date and time types - 8.1.7. strftime() and strptime() Behavior

Explanation:

df = datetime.datetime.strptime(dfield, '%Y/%m/%d %H:%M:%S')

Tell python that your date field timestamp_cst contains a date. Python now stores this as a datetime object df.

d1 = datetime.datetime.strptime('2017-08-27 00:00:00', '%Y/%m/%d %H:%M:%S')
d2 = datetime.datetime.strptime('2017-08-27 12:00:00', '%Y/%m/%d %H:%M:%S')

Put your start/end dates (times) into datetime objects for comparison against your date field.

if df >= d1 and df < d2:
    return 1

This runs the comparison of the datetime objects, so you are comparing datetime against datetime against datetime, not string against string or string against date etc.

  • Ah, I see now. This worked perfectly, thanks for the explanation! – blwoods Oct 23 '17 at 15:48
  • @blwoods If you are going to use this regularly I would recommend you rename the d1 and d2 to something more memorable so you know what they actually represent in your calculation. – Midavalo Oct 23 '17 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.