The syntax varies depending on where the data is being fetched from (file gdb, shapefile, personal gdb, Oracle, DB2, etc.).
File Geodatabase, shapefiles, and file-based data such as dbf files:
Everything in May:
EXTRACT(MONTH FROM "MyDate") = 05
EXTRACT(HOUR FROM "MyDate") < 12
Personal Geodatabase (.mdb):
DATEPART("m", [MyDate]) = 05
DATEPART(month, MyDate) = 05
For more details such as further limitations and query by hour/year/etc. see the Esri HowTo: Search for specific parts of a date stored in a Date-Time field. For extended examples using Field Calculator see ArcWatch Simplify Date and Time Calculations.