Definition Query for Dates

[CMcDonald]

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 .

[DanielBrenner]

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

[Zeke]

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.

[jamesbanting]

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