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

I hope everyone well,

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

any help for this will be so appreciated.

Thank you so much

CodePudding user response：

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

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

CodePudding user response：

If the column TIMESTAMP in your table is number, and you want to convert it to date, use TO_DATE with the right format (see below)

I am using the alter session to just show you the format with hours minutes and seconds

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss' ;

Session altered.

SQL> select to_date('20200110135752','YYYYMMDDHH24MISS') from dual ;

TO_DATE('2020011013
-------------------
10.01.2020 13:57:52

So, in your case should be

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