1

I have two values I want to compare. Samples of those are the following:

  1. '01/04/2020T07.08.45'
  2. '2020-04-01 14:46'

I want to transform the first value into the same mask as the second, so I can use it to join two tables lateron.

The first value is saved in a different time-zone than the second. I also need to convert that by adding two hours to the first value, depending on the moment I ran the comparasing.

Using substring didn't solve my problem as I wasn't able to add two hours to a string. The to_date function also didn't brought me anything.

Can you help me?

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
Sunfile
  • 101
  • 1
  • 4
  • 22
  • 1
    Fix your data so you are storing dates as `date`s. There is a reason why there are built-in data types. Don't use strings inappropriately. – Gordon Linoff May 01 '20 at 14:15
  • You said you have to add two hours. What about daylight saving time? Dates close to 1st April might be sensitive to that. Why on earth do you store Date values as strings? – Wernfried Domscheit May 01 '20 at 18:17
  • What are the time zones of the two values? – Wernfried Domscheit May 01 '20 at 18:18
  • Convert your strings to dates (using the `to_date` function). Then you can use the Oracle database built-in date-time [functions](https://www.oracletutorial.com/oracle-date-functions/). – Abra May 01 '20 at 23:35
  • Thanks for your comments, I'm not the database designer so I have to deal with what I get. I'm aware of the daylight saving time so will need to adapt the script manually depending on date values – Sunfile May 02 '20 at 22:24

1 Answers1

1

You have to convert both values to the date.

TO_DATE('2020-04-01 14:46', 'YYYY-MM-DD HH24:MI');
TO_DATE('01/04/2020T07.08.45', 'DD/MM/YYYYTZH.HH24.MI')

I'm not sure about the date that contains a timezone.

Widada
  • 575
  • 2
  • 4
  • 17