CODEWITHSUNDEEP
oracle-databasetimestamp
user
user

Reputation: 167

oracle concat timestmap from current_date

I hardly try to concat timestamps. Column time, defined as varchar2, contains values like 23:15. Now I want to create a timestamp with today's date and that time, in this example 23.03.18 23:15:00.00000 is expected. The way I'm doing this is 

to_timestamp(to_char(trunc(current_date),'ddMMyyyy') ||
             to_char(time),'dd.MM.yyyy hh24:mi:ss')

and it works. But when field time has value 06:15, I get the message "hour must be between 0 and 23 ". Whatever I try, it's always this message when time value has leading zero. How can that be corrected?

Upvotes: 1

Views: 203

Answers (3)

Littlefoot
Littlefoot

Reputation: 143013

Works OK for me:

SQL> WITH test
  2       AS (SELECT '23:15' time FROM DUAL
  3           UNION
  4           SELECT '06:15' time FROM DUAL)
  5  SELECT TO_TIMESTAMP (
  6            TO_CHAR (CURRENT_DATE, 'ddMMyyyy') || time,
  7            'dd.MM.yyyy hh24:mi:ss') result
  8    FROM test;

RESULT
----------------------------------------------------------------
23.03.18 06:15:00,000000000
23.03.18 23:15:00,000000000

SQL>

Please, post your SQL*Plus session so that we could see what you did.

Upvotes: 0

Emre
Emre

Reputation: 387

try other way around: insert a leading 0 if hour < 10

to_timestamp(to_char(trunc(current_date),'ddMMyyyy') ||
             to_char(
                       case when substr(time,1,instr(time,':',1,1)-1)<10 
                               then'0'||time  
                            else time 
                       end),'dd.MM.yyyy hh24:mi:ss')

Upvotes: -1

MT0
MT0

Reputation: 168361

You don't need to use TRUNC on the current date as the TO_CHAR will extract only the year-to-day components and you also don't need TO_CHAR on the time column as it is already a string. Apart from those simplifications, your query works:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE times ( time ) AS
SELECT '00:00' FROM DUAL UNION ALL
SELECT '06:45' FROM DUAL UNION ALL
SELECT '12:00' FROM DUAL UNION ALL
SELECT '18:59' FROM DUAL UNION ALL
SELECT '23:15' FROM DUAL;

Query 1:

SELECT time,
       TO_TIMESTAMP(
         TO_CHAR( CURRENT_DATE, 'YYYY-MM-DD' ) || time,
         'YYYY-MM-DDHH24:MI'
       ) AS current_day_time
FROM   times

Results:

|  TIME |      CURRENT_DAY_TIME |
|-------|-----------------------|
| 00:00 | 2018-03-23 00:00:00.0 |
| 06:45 | 2018-03-23 06:45:00.0 |
| 12:00 | 2018-03-23 12:00:00.0 |
| 18:59 | 2018-03-23 18:59:00.0 |
| 23:15 | 2018-03-23 23:15:00.0 |

Upvotes: 2

Related Questions