CODEWITHSUNDEEP
sqloracle-database
ScottW
ScottW

Reputation: 69

Time stored in VARCHAR field Add to Date for Date/Time

I have a StartTime that is stored in an Oracle db as a Varchar. I also have a date field. I'd like to create a Date/Time field. Can I please have help combining these two? Much appreciated!

My time field is like this:

SITE_START_TIME
11:05
01:23
09:30
09:40
12:30

My Date Field is like this:

09-SEP-21
21-SEP-21
30-SEP-21
10-SEP-21
01-SEP-21

Upvotes: 1

Views: 211

Answers (2)

MatBailie
MatBailie

Reputation: 86798

This is a similar question to... adding time to date in oracle

Which gives...

date_column + to_dsinterval('0 ' || site_start_time || ':00')

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231851

One option would be

to_date( to_char( date_column, 'yyyy-mm-dd' ) || ' ' || site_start_time,
         'yyyy-mm-dd hh24:mi' );

You could use the string to generate an interval and add that to the actual date as well

date_column +
  to_dsInterval( '0 ' || site_start_time || ':00' )

Note that an Oracle date always has a day and a time component. So it doesn't really make sense to store a day and a time in separate columns. Just store the actual date value in the date column complete with the time.

Upvotes: 2

Related Questions