CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
UI_Dev
UI_Dev

Reputation: 3417

How to get timestamp for date column in oracle sql?

I am having a table name called "users". It has one column named "logondt" in which it will show only in this format mm:dd:yy like 04-JUN-14. But I need to use select query to find in which time, the user is logged in.

I tried something like this..

select logondt from users where logondt between to_date('2014-06-03' ,'yyyy-mm-dd') and to_date('2014-06-03' ,'yyyy-mm-dd') and userid='12345';

Is it possible?

Upvotes: 5

Views: 112268

Answers (1)

ngrashia
ngrashia

Reputation: 9894

Use TO_CHAR function

SELECT TO_CHAR(LOGONDT , 'DD/MM/YYYY HH:MI:SS') LOGONDT 
FROM USERS
WHERE 
LOGONDT BETWEEN to_date('2014-06-03' ,'yyyy-mm-dd') 
        and to_date('2014-06-03' ,'yyyy-mm-dd') 
AND USERID = '12345';

To Get in 24 HR format, use 

TO_CHAR(LOGONDT , 'DD/MM/YYYY HH24:MI:SS')

Upvotes: 19

Related Questions