topic Re: Definition Query for Dates in Data Management Questions

Definition Query for Dates

CMcDonald — Thu, 15 Apr 2010 09:48:47 GMT

Hi,
Im struggling to create a query that will pick up dates in a date field. I have tried several versions with ' and " and brackets and all sorts. I'm using the 'Get Unique Values' button to so should be simple?
Field Name: CLOSE_DATE
Layer: NSG_Lines
DB: CORPDATA
My Query: CLOSE_DATE = date '1899-12-30 00:00:00'
The Error: There was an error with the expression. Underlying DBMS error [ORA-01861: literal does not match format string][CORPDATA.NSG_Lines]

The data is sitting in Oracle 9i

Cheers,
Chris .

Re: Definition Query for Dates

DanielBrenner — Tue, 08 Feb 2011 21:34:50 GMT

Chris,

I had the exact same date (a default?) come up for me when I was trying to create a Date/Time field in ArcPAD 10 and I am going nuts trying to get at the 'code' that creates the format of
YYYY-MM-DD HH:MM:SS.

What I want to know from you is how to get the date/time field to change automatically?

Thanks,

Dan B
Eureka, CA

Re: Definition Query for Dates

Zeke — Tue, 08 Feb 2011 23:17:44 GMT

Never used Oracle, but here's one result from googling 'Oracle date format':
http://www.oradev.com/oracle_date_format.jsp

The 1899 date may be the base that the db starts from. Dates are generally represented internally to the computer as the number of days since a base date (1899... in this case, apparently), then displayed as a regular date. At least that's how they used to be done.

I don't think there's any way to update a date/time field automatically within the field itself. You could use a trigger to update at specified intervals or events, assuming your db is running at the time. You probably wouldn't want constant updating unless you have a lot of processing power anyway. Updating every second, even every minute, would use a lot of processing resources.

edit: It's been a while since I've actually done any of this, so take the above with a grain of salt.

Re: Definition Query for Dates

jamesbanting — Tue, 26 Apr 2011 21:23:59 GMT

Hi Dan,

Did you ever find out how to get that format? I'm trying to do that same thing in arcpad 8

Thanks,
James