to_date('30/03/2022', 'DD/MM/YYYY')
Underlined, as hours are not specified, that means that hour is '00:00' I would like to specify that this is for Europe/Paris time zone region. Can you help me set-up this ? Thanks
A DATE data type has the components: year, month, day, hour, minute and second. It ALWAYS has those components and NEVER stores anything else (such as a time zone); so it is impossible to store a time zone in a DATE data type.
A TIMESTAMP data type has the components: year, month, day, hour, minute and second and, optionally, can store fractional seconds.
A TIMESTAMP WITH TIME ZONE data type has the components: year, month, day, hour, minute, second and time zone and, optionally, can store fractional seconds information.
Therefore, if you want to store a time zone then you should use TIMESTAMP WITH TIME ZONE and not DATE.
Your code would then be:
TO_TIMESTAMP_TZ('30/03/2022 Europe/Paris', 'DD/MM/YYYY TZR')
or using a timestamp literal:
TIMESTAMP '2022-03-30 00:00:00 Europe/Paris'
or, if you want to pass in your date in that format and add the time zone in a two-step process:
FROM_TZ(TO_TIMESTAMP('30/03/2022', 'DD/MM/YYYY'), 'Europe/Paris')
db<>fiddle here