SelectLayerByAttribute_management issue, need help

[JohnRitsko]

Trying to work my way through a PGDB and update the field PROJECT_STATUS depending on data in other fields.  My problem seems to be in the query portion of theelectLayerByAttribute.  I've tried to many iterations of this.  Eventually I wanted to update this data directly in SDE on our SQL Server but I think I can figure that out once I get this.  Thank you..

BELOW IS A WORKING SCRIPT.  Thank you all.

#### updated script
# Import arcpy module
import arcpy, time, sys
from arcpy import env

env.overwriteOutput = True
env.workspace = "Database Connections//GISSDE.RTC.sde"
arcpy.MakeFeatureLayer_management("RTC.GISSDE.MPO_CMWG_L_TEST","cmwg_lyr")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"\"", "VB", "")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "[ACTUAL_DESIGN_START] = 1")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"DESIGN\"", "VB", "")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "[ACTUAL_NTP] = 1")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"CONSTRUCTION\"", "VB", "")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "[ACTUAL_SUBSTANTIAL_COMPLETION] <= CURRENT_TIMESTAMP")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"SUBSTANTIAL COMPLETION\"", "VB", "")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "RTC_PROJECT_CLOSED <= CURRENT_TIMESTAMP")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"CLOSED\"", "VB", "")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "[PROJECT_STATUS] IS NULL OR [PROJECT_STATUS] = ''")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"PLANNED\"", "VB", "")

[JamesCrandall]

What is CURRENT_DATE?

[JohnRitsko]

CURRENT_DATE is me trying to get the current date to compare against.  I don't yet have a way of getting the Current Date in there.  I'm now working on updating my data in SDE.  The field that I'm trying to create the query against is a DATE field so I need to compare todays date against that field.  Trying to figure out how to get todays date in python to match what is in SDE in SQL Server.

Note: in SQL Server we store the dates that I require my compare against as datetime2 fields.

This is how the present value is displayed.

2016-01-28 08:31:18.568000

In SQL Server the date field (ACTUAL_SUBSTANTIAL_COMPLETION)

shows 2015-09-25 00:00:00.000

present = datetime.now()
'{:%Y-%m-%d %H:%M:%S}'.format(present)

where = "[ACTUAL_SUBSTANTIAL_COMPLETION] <= " + str(present)
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", where)
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"SUBSTANTIAL COMPLETION\"", "VB", "")

[JamesCrandall]

This should get you the datetime, but you may need to go thru some testing to get it just right (trust me, dates are just loads of fun to deal with).

import datetime
current_date = datetime.datetime.now()
print current_date

CURRENT_DATE in SQL?  DO you mean CURRENT_TIMESTAMP?

[JohnRitsko]

Yeah, it's driving me nuts to be honest. 

The Current_DATE that I mentioned was me simply trying to figure out the current date in a format that would allow my query to work.

In SDE I have a feature class that I'm trying to access the ACTUAL_SUBSTANTIAL_COMPLETION field which is a date field.  I want to compare that field against todays date and if it's <= to today then I can calculate a value into the PROJECT_STATUS field.  I hope this helps get you a better understanding of what I'm doing.

When I create the query it continues to tell me invalid expression.

[JohnRitsko]

Thanks James.  That did the trick sir.  Much appreciated.

[JohnRitsko]

I just wanted to thank everyone for all their input.  Initially I wanted to utilize cursors and today I'll see if I can resolve the question using them.  What I have works for the time being and yes, utilizing cursors and if else statements will be my final goal.  I'll work through the code again and will post my cursor version once I get it working for me. 

Again, thank you all who have taken the time for the help.