CODEWITHSUNDEEP
sqloracle-databasedateselect
Ruser-lab9
Ruser-lab9

Reputation: 315

How to convert a date to a string

I want yo get only the 'date hours:minutes:seconds' from the Date column

Date
10/11/22 12:14:01,807000000
11/12/22 13:15:46,650000000
29/12/22 14:30:46,501000000

and I want to get a string column with date hours:minutes:seconds

Date_string
10/11/22 12:14:01
11/12/22 13:15:46
29/12/22 14:30:46

I tried this code but it doesn't work:

select*, TO_CHAR(extract(hour from (Date)))||':'||TO_CHAR(extract(minute from (Date)))||':'||TO_CHAR(extract(second from (Date))) as Date_string
from table;

Upvotes: 1

Views: 319

Answers (4)

Mureinik
Mureinik

Reputation: 311338

If this is a date column, you could use to_char directly:

SELECT m.*, TO_CHAR(my_date_column, 'dd/mm/yy hh24:mi:ss')
  FROM mytable m

Upvotes: 2

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Going by what you have in your question, it appears that the data in the field Date is a timestamp. This isn't a problem, but the names of the table (TABLE) and field (Date) present some challenges.

In Oracle, TABLE is a reserved word - so to use it as the name of a table it must be quoted by putting it inside double-quotes, as "TABLE". Similarly, Date is a mixed-case identifier and must likewise be quoted (e.g. "Date") every time it's used.

Given the above your query becomes:

SELECT TO_CHAR("Date", 'DD/MM/YY HH24:MI:SS') AS FORMATTED_DATE
  FROM "TABLE"

and produces the desired results. db<>fiddle here

Generally, it's best in Oracle to avoid using reserved words as identifiers, and to allow the database to convert all names to upper case - if you do that you don't have to quote the names, and you can refer to them by upper or lower case as the database automatically converts all unquoted names to upper case internally.

Upvotes: 0

marcothesane
marcothesane

Reputation: 6731

Just do it with the TO_DATE() and TO_CHAR() function pair, both operating on the Oracle date format strings:

Building the scenario:

-- your input ..
WITH indata(dt) AS (
SELECT '10/11/22 12:14:01,807000000' FROM dual UNION ALL
SELECT '11/12/22 13:15:46,650000000' FROM dual UNION ALL
SELECT '29/12/22 14:30:46,501000000' FROM dual
)
-- end of your input. Real query starts here. 
-- Change following comma to "WITH" ..
,
-- Now convert to TIMESTAMP(9) ...
as_ts AS (
  SELECT
    TO_TIMESTAMP(dt ,'DD/MM/YY HH24:MI:SS,FF9') AS ts
  FROM indata
)
SELECT
  ts
, CAST(ts AS TIMESTAMP(0)) AS recast -- note: this is rounded
, TO_CHAR(ts,'DD/MM/YY HH24:MI:SS') AS reformatted -- this is truncated
FROM as_ts

Result:

TS RECAST REFORMATTED
10-NOV-22 12.14.01.807000000 10-NOV-22 12.14.02 10/11/22 12:14:01
11-DEC-22 13.15.46.650000000 11-DEC-22 13.15.47 11/12/22 13:15:46
29-DEC-22 14.30.46.501000000 29-DEC-22 14.30.47 29/12/22 14:30:46

Upvotes: 0

jose_bacoy
jose_bacoy

Reputation: 12684

You can use REGEX SUBSTRING function to get the date string on the left.

SELECT  REGEXP_SUBSTR (Date_string, '[^,]+', 1, 1)    
    AS left_part
   FROM Table1;

where ^, means look for chars that are NOT comma on 1st position 
and get the first occurrence (on the left)

Result:

LEFT_PART
10/11/22 12:14:01
11/12/22 13:15:46
29/12/22 14:30:46

reference: https://docs.oracle.com/cd/B12037_01/server.101/b10759/functions116.htm

Upvotes: 0

Related Questions