3 Replies Latest reply on Apr 26, 2011 2:23 PM by jbanting

    Definition Query for Dates

    cmcdonald@aberdeen
      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
          DanBear
          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
            recurvata
            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
              jbanting
              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