I was building a search from for events, where the user could search by full date or any part of the date(day, month, or year). Since the events can start and end at odd hours of the day, I needed to use the full datetime format, i.e. Jan 16 2002 12:00AM. When trying to search for a specific month or year, the logical thing would be to use a substring function to parse the field data. But, since you can’t use SQL’s built-in function SUBSTRING() on a datetime field (at least not in MSSQL), you have to use a combination of LEFT() and RIGHT(), which are essentially substring functions. Go figure.
Example to check for a particular year, say 2001, where mydate is the db fieldname:
LEFT((RIGHT(mydate, 12)),4)=’2001′
To check for March 2002:
LEFT(mydate, 3)=’Mar’ and LEFT((RIGHT(mydate, 12)),4)=’2002′
I ended up using the same thing for month/day searches as well.
