Hi I am trying to create a sql script that inserts data from a staging table to the main table and in the staging table I have a column called datetime that is a varchar field.
I am inserting all columns plus converting the datetime field to the field in the main table that has a datatype of time stamp.
Data in im DateTime staging table looks like this.
‘10-JUN-16 12:00 AM’
I tried this below and it didn’t work. I got an error message saying. a non-numeric character was found where numeric was expected. My assumption is the AM is or the JUN is throwing it off.
INSERT INTO meterdatarows
SELECT ‘HardCodedField’,
meterid,
TO_TIMESTAMP (datetime, ‘yyyy/mm/dd hh24:mi:ss’),
value
FROM meterdatarowsstaging commit;
capjlp, 1 - you should always identify the full version of Oracle in use 2 - you should list the Oracle error being received. In your post your data does not match your format string. Your data is not using a 24 hour clock (HH24) and does not contain seconds information so why would you lie to Oracle about what your data looks like? The format should match the data. Oracle will default the missing time values.
I tried just inserting the data without the to_timestamp data however got the same error. I wonder if I should change the datatype of this column to just date however I do need the time included. I need to convert the data to the 24hr format. I just don’t know how.
What you’re doing is totally fine. You just have your date format incorrect in your to_timestamp. It should match your varchar field’s format. ‘DD/MON/YY HH:MM AM’ looks to be what you have in your varchar, so use that.
Before you go down the line of using a varchar to store dates and times, please don’t!
The DATE data type stores the date and time with the seconds included. The TIMESTAMP goes down to fractions of a second.
If you use a varchar you are asking for trouble. Plus, the cost based optimiser will not be able to give you the best execution plan if you use the wrong data types in a query.
A date (and time) is not a varchar, please don’t store them in one. Always use the correct data type for the data being stored. Varchar columns store character data, number types do numeric data, and dates with or without times go into DATE or TIMESTAMP columns.
Please!
Cheers,
Norm. [ TeamT ]
–
Sent from my Android device with K-9 Mail. Please excuse my brevity.
