Select by Attribute SQL Expression using Dates:

MarkEnglish

Hello,
I have a table with Expiration Dates.  I want to use the select by attributes to tell me what dates are between now and exactly one year.  Thanks in advance for any help.
Mark

RichardFairhurst

Hello,
I have a table with Expiration Dates.  I want to use the select by attributes to tell me what dates are between now and exactly one year.  Thanks in advance for any help.
Mark

I am going to assume you are using a File geodatabase, since I am most familiar with that database and you did not say what database you actually are using.  All SQL help is dependent on the database you are working with, so until you provide that information you really can't get help that deals with your specific needs.

This expression will select all records based on the current date and within the last year when time of day is not a factor (accounting for leap year):

(EXTRACT(MONTH FROM CURRENT_DATE) = EXTRACT(MONTH FROM CURRENT_DATE - 365) AND EXTRACT(DAY FROM CURRENT_DATE) = EXTRACT(DAY FROM CURRENT_DATE - 365) AND "CREATED" <= CURRENT_DATE AND "CREATED" > CURRENT_DATE - 365) OR (EXTRACT(MONTH FROM CURRENT_DATE) = EXTRACT(MONTH FROM CURRENT_DATE - 366) AND EXTRACT(DAY FROM CURRENT_DATE) = EXTRACT(DAY FROM CURRENT_DATE - 366) AND "CREATED" <= CURRENT_DATE AND "CREATED" > CURRENT_DATE - 366) OR (EXTRACT(MONTH FROM CURRENT_DATE) = 2 AND EXTRACT(DAY FROM CURRENT_DATE) = 29 AND "CREATED" <= CURRENT_DATE AND "CREATED" > CURRENT_DATE - 366)

This expression will select all records based on the current date and time stamp to immediately after the exact same time on the same date 1 year ago (accounting for leap year):

(EXTRACT(MONTH FROM CURRENT_TIMESTAMP) = EXTRACT(MONTH FROM CURRENT_TIMESTAMP - 365) AND EXTRACT(DAY FROM CURRENT_TIMESTAMP) = EXTRACT(DAY FROM CURRENT_TIMESTAMP - 365) AND "CREATED" <= CURRENT_TIMESTAMP AND "CREATED" > CURRENT_TIMESTAMP - 365) OR (EXTRACT(MONTH FROM CURRENT_TIMESTAMP) = EXTRACT(MONTH FROM CURRENT_TIMESTAMP - 366) AND EXTRACT(DAY FROM CURRENT_TIMESTAMP) = EXTRACT(DAY FROM CURRENT_TIMESTAMP - 366) AND "CREATED" <= CURRENT_TIMESTAMP AND "CREATED" > CURRENT_TIMESTAMP - 366) OR (EXTRACT(MONTH FROM CURRENT_TIMESTAMP) = 2 AND EXTRACT(DAY FROM CURRENT_TIMESTAMP) = 29 AND "CREATED" <= CURRENT_TIMESTAMP AND "CREATED" > CURRENT_TIMESTAMP - 366)

I am using matching date logic for leap years rather than end of the month logic so that 2/28/2013 looks back to 2/28/2012.  I have not worked out how to get end of the month logic to work so that you go from 2/28/2013 back to 2/29/2012.

Speaking to the programmer for the file geodatabase at the ESRI UC this year I found out that file geodatabase SQL complies with all by a few SQL 92 specifications, so you can look up help on the internet for the SQL 92 syntax and normally apply it to the file geodatabase.  That is how I found out that CURRENT_TIMESTAMP works with a file geodatabase.