CODEWITHSUNDEEP
sqloracle-database
alim menz
alim menz

Reputation: 31

Convert number to date in sql and fetch the data from the daily date

I hope everyone well,

Anyone can help me to fetch the daily date data , my table look like

enter image description here

any help for this will be so appreciated.

Thank you so much

Upvotes: 0

Views: 40

Answers (1)

MT0
MT0

Reputation: 167981

Use TO_DATE:

SELECT book,
       library,
       TO_DATE(timestamp, 'YYYYMMDDHH24MISS') AS timestamp,
       count
FROM   my_table

Which, for the sample data:

CREATE TABLE my_table (book, library, timestamp, count) AS
SELECT 'History',  'Alexandra', 20200110133752, 32 FROM DUAL UNION ALL
SELECT 'action',   'Liba',      20200112133752, 44 FROM DUAL UNION ALL
SELECT 'Learning', 'PML',       20200113133752, 53 FROM DUAL;

Outputs (with the NLS_DATE_FORMAT as YYYY-MM-DD HH24:MI:SS):

BOOK LIBRARY TIMESTAMP COUNT
History Alexandra 2020-01-10 13:37:52 32
action Liba 2020-01-12 13:37:52 44
Learning PML 2020-01-13 13:37:52 53

db<>fiddle here

Upvotes: 1

Related Questions