Select by Attribute SQL Expression using Dates:

MarkEnglish — Wed, 17 Jul 2013 12:49:15 GMT

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

Re: Select by Attribute SQL Expression using Dates:

JoeBorgione — Wed, 17 Jul 2013 14:10:32 GMT

This has worked for me. It creates a a new table by selecting from one table INTO another. It's the between statement you want. Your mileage may vary and sorry, no tech support is available...

SELECT    Field1, Field2 Field3, Field_ETC_ETC
INTO           db.dbowner.tablename2
FROM         db.dbowner.tablename1
WHERE     (YourDateField BETWEEN '2013/03/19 00:00' AND '2013/06/10 00:00')
ORDER BY YourDateField

Here is a great site for learning sql expressions

Re: Select by Attribute SQL Expression using Dates:

RichardFairhurst — Wed, 17 Jul 2013 16:04:30 GMT

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.

topic Re: Select by Attribute SQL Expression using Dates: in Geoprocessing Questions

Select by Attribute SQL Expression using Dates:

Re: Select by Attribute SQL Expression using Dates:

Re: Select by Attribute SQL Expression using Dates: