I have ID's stored into the ID column in table TEMP_SDR_RECEIVED which has value for example: 201505264865645884. The year,month and date is stored in this ID format in the first 8 numbers. I want to extract the year,month,date from this ID and want to set the minute,hour,seconds to 00:00:00 and others values to null in the column ID_DATE which is date data type. So the conversion of ID into date format should look like this : 26.05.15 00:00:00 I am trying following query but getting an error as the hour must be between 1 to 23. Here is my query:
Insert into TEMP_SDR_RECEIVED(ID_DATE)
Select to_date(substr(ID, 1, 14), 'YYYYMMDDHH24MISS') FROM TEMP_SDR_RECEIVED
You only need the 8 first characters then :
Insert into TEMP_SDR_RECEIVED(ID_DATE)
Select to_date(substr(ID, 1, 8), 'YYYYMMDD') FROM TEMP_SDR_RECEIVED
See SQLFiddle.
By default, the hours / min / seconds will be set to 0
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments