topic Re: SQL QUERY for data format in Data Management Questions

SQL QUERY for data format

benberman — Wed, 24 Jul 2013 18:07:15 GMT

I am trying to create an SQL expression that will test against the data formats that are in a particular field. All the entries in the field should of DATE format. Suggestions are welcome.

Thanks.

Re: SQL QUERY for data format

AnthonyGiles — Wed, 24 Jul 2013 18:12:59 GMT

If you are using a file geodatabase then use some thing like:

"Datefield" = date'2001-06-30 19:30:00'

For other databases the syntax maybe different check out this help page:

http://resources.arcgis.com/en/help/main/10.1/index.html#//00s500000033000000

Regards

Anthony

Re: SQL QUERY for data format

benberman — Wed, 24 Jul 2013 18:21:10 GMT

I am aware of how to test against a particular value. I will needing an SQL quert to test against Data FORMAT. Something to the likes of, "date_field" IS NOT FORMAT(DATE)

So far, have not found anything. Suggestions welcome.

Re: SQL QUERY for data format

AnthonyGiles — Wed, 24 Jul 2013 18:29:08 GMT

I'm not quite sure what you are trying to achieve the field type is set in the schema of the database. If a field is set to date then all values in that field have to be a date.

Regards

Anthony

Re: SQL QUERY for data format

VinceAngelo — Thu, 25 Jul 2013 11:15:19 GMT

If you're looking for an RDBMS-specific solution to regular expression matching
in text fields to see if they conform to date formats, then the appropriate RDBMS
vendor's web site will likely be of most assistance. I doubt File Geodatabases
support any such SQL function (in which case you'd need to walk the cursor
set on the table and use programming language functions on the text strings).

- V