How to get timestamp for date column in oracle sql?

Question

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?

Answer 1

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')