Skip to main content
Solved

how to save an attribute to an oracle date type column?

arthy
Contributor
Forum|alt.badge.img+8
  • arthyContributor
  • Contributor

Hi,

 

 

I have an attribute (timeattribute) that I would like to save to an oracle table
  timeattribute = '2017-06-30 18:39:05'

Within a sqlexecuor, I'm doing something like: 

INSERT INTO TEST_T
  (
    OBJECTID,    
    TIMEATTRIBUTE
  )
  VALUES
  (
    @Value(OBJECTID),
   '@Value(TIMEATTRIBUTE)'
   )    

and it failed. How can I make it work?

 

FYI, the query
INSERT INTO TEST_T
  (
    OBJECTID,    
    TIMEATTRIBUTE
  )
  VALUES
  (
    @Value(OBJECTID),
   sysdate
   )   

worked perfectly within the sqlexecutor.

Thanks

Best answer by david_r

This is more of an Oracle question, you'll have to use the to_date() function. Example:

INSERT INTO TEST_T
  (
    OBJECTID,    
    TIMEATTRIBUTE
  )
  VALUES
  (
    @Value(OBJECTID),
   to_date('@Value(TIMEATTRIBUTE)', 'YYYY-MM-DD HH24:MI:SS')
   ) 
View original
Did this help you find an answer to your question?

4 replies

ebygomm
Influencer
Forum|alt.badge.img+31
  • ebygommInfluencer
  • Influencer
  • July 4, 2017

You need to use the to_date function to insert into oracle, e.g.

david_r
Evangelist
  • david_rEvangelist
  • Best Answer
  • July 4, 2017

This is more of an Oracle question, you'll have to use the to_date() function. Example:

INSERT INTO TEST_T
  (
    OBJECTID,    
    TIMEATTRIBUTE
  )
  VALUES
  (
    @Value(OBJECTID),
   to_date('@Value(TIMEATTRIBUTE)', 'YYYY-MM-DD HH24:MI:SS')
   ) 
david_r
Evangelist
  • david_rEvangelist
  • July 4, 2017

Just another thing, you should be careful about manually inserting OBJECTID values unless you've queried the relevant SDE function for the next value. The issue is that the OBJECTID column is tied to an Oracle sequence and if you insert your own values without simultaneously updating the sequence you risk primary key collisions down the line.

From the ESRI FAQ: http://support.esri.com/en/technical-article/000008996

More technical info here: http://desktop.arcgis.com/en/arcmap/10.3/manage-data/using-sql-with-gdbs/next-rowid.htm

arthy
Contributor
Forum|alt.badge.img+8
  • arthyContributor
  • Author
  • Contributor
  • July 4, 2017
david_r wrote:

This is more of an Oracle question, you'll have to use the to_date() function. Example:

INSERT INTO TEST_T
  (
    OBJECTID,    
    TIMEATTRIBUTE
  )
  VALUES
  (
    @Value(OBJECTID),
   to_date('@Value(TIMEATTRIBUTE)', 'YYYY-MM-DD HH24:MI:SS')
   ) 
@david_r,

 

Thanks

 

 

Reply


Helpful Members This Week

Community Stats

30,894
Posts
117,082
Replies
38,700
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.