How To: Convert Dates from Text to True Date Objects

Procedure

Description

When importing a DBMS table into ArcView using SQL Connect, the records in the date fields have a time component added to them and the date objects get converted into text (strings). Also, some of the number fields come into ArcView with a decimal precision of six, but the original numbers have less than six places to the right of the decimal.

Instructions provided show how to get rid of the time component, how to convert the dates from text back into true date objects, and how to get rid of the extra decimals.

Procedure

In most databases, date objects contain date and time. Even if a particular field type displays only the date, there is still a hidden time component to each record. ArcView tables are based upon the DBase and INFO models, where date fields do not include time components. In these models, date fields are only eight characters long. The first four characters are for the year, two for the month, and the last two are for the day; for example, 20000612, which is equivalent to 06/12/2000. When SQL Connect is used to display a database table with date fields in a project, ArcView detects that the values in the date fields have more than eight characters (because of the time component). This is true even if the time component is hidden when displayed in the table's native software. ArcView must find a way to display all the information in date fields, including the time component. The only way ArcView can do this is by converting the date objects into text strings and placing them into a special type of text (string) field called ISODATETIME. Many users, however, do not want the time component in their SQL tables. Also, since the dates are converted into text strings, date query operations cannot be made on them.

The following script creates a new file in dBASE format that is a replica of the SQL table. The ISODATETIME fields are replaced with true DATE fields. The text values in the ISODATETIME fields are converted back into date objects, without a time component. Any other field types in the SQL table are carried over into the DBase table unchanged.

1. Open a new script window.

2. Copy the script into the new window.

Code:
'--- This script takes an sql table with ISODATETIME
'--- fields and creates a replica of it in dBase
'--- format. In the replica, the ISODATETIME fields
'--- are replaced with DATE fields
'--- This script is designed to run in ArcView 3.1.
'--- To run it in ArcView 3.0, see the bottom of the script.

'-- Get a list of the fields in the sql table
theTable=av.getactivedoc
Vtab1 = theTable.getVtab
VTab1FieldsList = VTab1.GetFields

'-- make a list of the ISODATETIME fields in
'-- the sql and a list of those field's aliases.

sqlDateFields = {}
sqlDateFieldsAliasList = {}
for each f in VTab1FieldsList
 if (f.gettype = #FIELD_ISODATETIME) then
   sqlDateFields.Add(f)
   alias=f.GetAlias
   sqlDateFieldsAliasList.add(alias)
  end
end

'-- Make a new vtab (vtab2), make it editable, and 
'-- open it's window
theFileName=FileDialog.Put
  (av.GetProject.MakeFileName
  ("table", "dbf"),"*dbf","Export Table")
if (theFileName = NIL) then return nil 
end

VTab2 = VTab.MakeNew(theFilename,DBase)
newTableDoc = Table.Make(VTab2)
newTableDoc=table.Make(Vtab2)
newTableDoc.GetWin.Open
VTab2.SetEditable(true)

'-- add to vtab2 the same number of records in vtab1
tempField = Field.Make("temp",#FIELD_CHAR  ,0,0)
VTab2.AddFields({tempField})
for each r1 in VTab1
  VTab2.addrecord
end

'-- populate the records in vtab2 with values 
'-- derived from vtab1
Date.SetDefFormat("yyyy MM dd") '* see below

For each f1 in VTab1FieldsList
  fName = f1.GetName 'orignally GetAlias
  fType = f1.GetType
  fWidth = f1.GetAlias.Count
  if (fWidth < 8) then
    fWith = 8
  end
     If (ftype = #FIELD_ISODATETIME) then
    dateField = Field.Make
      (fName,#FIELD_DATE,fWidth,0)
      Vtab2.AddFields({dateField})
      For each r2 in VTab1
        theValue1 = VTab1.ReturnValue(f1,r2)
        
        if (thevalue1 <> "") then
          theDate = 
            theValue1.Extract(0).Translate
              ("-"," ").AsDate                            ' **
            VTab2.SetValue
              (VTab2.FindField(dateField.GetAlias),
              r2,theDate)
            
         else 
         
           VTab2.SetValue
           (VTab2.FindField(dateField.GetAlias),
              r2,nil)
         end
      end
    else
    VTab2.AddFields({f1.clone})
      for each r3 in VTab1
        theField = VTab2.FindField(fName)
        theValue3 = VTab1.ReturnValue(f1,r3) 
        VTab2.SetValue(theField,r3,theValue3)  
      end
  end
end

VTab2.flush
VTab2.refresh

'-- remove the temp field. stop editing vtab2
    
VTab2.RemoveFields({tempField})
VTab2.SetEditable(false) 

'--- End of Script

To run this script in ArcView 3.0, substitute the
following lines with the indicated lines in the script:

'--*      Date.SetDefFormat("MM dd yy")
'--**      ("/"," ").AsDate

3. Compile the script.

4. Attach the script to a button on the Table GUI.

5. Make the SQL table the active document.

6. Click the new button to run the script.

There is a prompt to specify a name and location for the resulting dBASE file, and ArcView creates the file and adds it to the project as a table.

This script has been incorporated into an extension, which is available on the ESRI ArcScripts Web page. Click the link below and do a keyword search for "SQL Table Field Reformatter."

Article ID:000001500

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Related Information

ArcScripts

Convert dates from text to true date objects

Procedure

Related Information

Discover more on this topic