0

I am having date as 0/0/0000 10:23:56 AM in a column.

I have tried with TO_CHAR(column_dt,'hh24:mm:ss') and TO_CHAR(column_dt,'hh:mm:ss pm'), but both are returning 00:00:00 and 00:00:00 00 respectively.

Can any one please help me out ?

Jérémie Bertrand
  • 3,025
  • 3
  • 44
  • 53
Soumya
  • 19
  • 4
  • Following link will help you: http://stackoverflow.com/questions/17104414/how-to-extract-only-time-from-a-datetime-field-in-oracle-sql-developer – Parth Patel Sep 18 '15 at 07:03
  • 2
    What datatype is the column column_dt, is it a DATE or VARCHAR2? – Ollie Sep 18 '15 at 07:03
  • 1
    `0/0/0000` is an invalid date, and Oracle won't let you store that. You can't possibly have that date. –  Sep 18 '15 at 07:29
  • I have this date ...The datatype is DATE – Soumya Sep 18 '15 at 08:20
  • If the column is defined as `DATE` then it can't possible have the value `0/0/0000` –  Sep 18 '15 at 09:25

2 Answers2

2

The "date" 0/0/0000 10:23:56 AM is not a valid date (minimum day and month is 01 and year must be different from 0) so i'm guessing that the column is varchar. If you want to extract the time from that string you can use something like this:

select SUBSTR('0/0/0000 10:23:56 AM', 10, 8) time,
SUBSTR('0/0/0000 10:23:56 AM', 10, 2) hh,
SUBSTR('0/0/0000 10:23:56 AM', 13, 2) MI,
SUBSTR('0/0/0000 10:23:56 AM', 16, 2) ss
  FROM DUAL
Cristian_I
  • 1,585
  • 1
  • 12
  • 17
0

Use like this : 

SELECT CONVERT(VARCHAR(20), Yourdatecolumn, 114)
AlexB
  • 7,302
  • 12
  • 56
  • 74
Jayanti Lal
  • 1,175
  • 6
  • 18