How change varchar and date to timestamp Oracle-CodePudding

in raport I create, I take data and separate hour in varchar. I must concat that and change type to timestamp

date from user '20220323' in data type hour from user '12:01:02' in string

and i must use this in where

where timestamp = date||' ' ||hour||,000000000

my format timestamp is '22/03/23 12:01:02,123456789'

Could you help me?

CodePudding user response：

You can use the function TO_TIMESTAMP() to convert string to datetime.
Credit to VBoka for his help. See below for format string parameters.

create table source_of_data (user_date date, user_hours varchar(100))

insert into source_of_data values(to_date('20220323', 'yyyymmdd'),  '12:01:02');

select to_timestamp(to_char(user_date, 'yyyymmdd') || ' ' || user_hours ,'YYYYMMDD HH:MI:SS') as_timestamp
from source_of_data;

| AS_TIMESTAMP                 |
| :--------------------------- |
| 23-MAR-22 12.01.02.000000000 |

db<>fiddle here

Parameter   Explanation

YYYY        4-digit year
MM          Month (01-12; JAN = 01).
MON         Abbreviated name of month.
MONTH       Name of month, padded with blanks to length of 9 characters.
DD          Day of month (1-31).
HH          Hour of day (1-12).
HH12        Hour of day (1-12).
HH24        Hour of day (0-23).
MI          Minute (0-59).
SS          Second (0-59).