String conversion to time

Question

I want my stored procedure to know how to convert a string which is in the format

MM-DD-YYYY (06-23-2008) to the TIMESTAMP(6) format 

The TIMESTAMP(6) format is as follows:

03-MAY-15 09.02.44.000000000 PM

because data in the table is stored as timestamp(6) and I want to use a string to select or modify the appropriate data.

So basically the above date would turn into

23-JUNE-08 00.00.00.000000000 PM 

and then I can loop trough till the end of the day to find the appropriate date

I am very very new to SQL so any guidance/tips are extremely appreciated

Thanks!

Answer 1

TIMESTAMP(6) doesn't have a string format on its own. It's just a temporal data type that doesn't have any formatting information, only date + time information.

To get a TIMESTAMP(6) value from a formatted string, you can use the TO_TIMESTAMP function, which will look something like this:

select to_timestamp('06-23-2008', 'MM-DD-YYYY') from dual

Once you have a TIMESTAMP(6) value, then you can choose to convert it to a string and format it any way you choose by using the TO_CHAR function.

For example:

select to_char(to_timestamp('06-23-2008', 'MM-DD-YYYY'), 'YYYY-MM-DD HH24:MI:SS') from dual

... will return:

2008-06-23 00:00:00

I hope this helps.