-1

I have a table in oracle 'punch' where punching data is stored. 'punchtime' column is having the data as varchar2 and time is stored(like 20:30:00). when i want to sort the data by punchtime I use this query

 select * from punch order by to_date(punchtime,'hh24:mi:ss')

This works perfectly. But the below query does not work

select * from punch where punchtime = to_date('01:00:00','hh24:mi:ss')

It shows "not a valid month"

Can anyone please help me. I have to query this column.

Shanna
  • 753
  • 4
  • 14
  • 34

1 Answers1

1

As because punchtime is varchar2 datatype column, You need to query :

select * from punch where to_date(punchtime,'hh24:mi:ss') = to_date('01:00:00','hh24:mi:ss')
ajmalmhd04
  • 2,582
  • 6
  • 23
  • 41
  • Yes; but for a single value, why not just compare the two string values directly, rather than converting both to dates? Even for a range they could be compared as strings, as the varchar2 order would be the same as the date (time) order in this case (in all locales as far as I'm aware). – Alex Poole Oct 10 '13 at 06:27
  • Thanks, Yes.. instead this can be done directly, but this would be a key solver for his error atleast:) – ajmalmhd04 Oct 10 '13 at 06:40