0

In oracle, when I search using below query, it is fetching wrong records (check the attached screenshot), can someone suggest the correct format for 12 hour time.

to_char(a.created, 'MM/DD/YYYY HH12:MI:SS') >='05/23/2012 12:00:00'

Thanks, Kiran.enter image description here

Kiranshell
  • 267
  • 3
  • 6
  • 12

1 Answers1

3

Don't search based on a string. Search based on a date. If you search on a string, you'll get string comparison semantics which are not what you want. The string '06/01/1900' is alphabetically after the string '05/23/2012' despite the date that it represents being much earlier.

a.created >= to_date('05/23/2012 12:00:00', 'mm/dd/yyyy hh24:mi:ss' )

or using a 12-hour clock

a.created >= to_date('05/23/2012 03:15:00 pm', 'mm/dd/yyyy hh:mi:ss am' )
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • thanks Justin, but how do I search for >=05/23/2012 03:15:00 pm – Kiranshell Sep 18 '12 at 04:18
  • @Kiranshell, Justin has given you an example based on the date/time you specified. If you want to change it to a different time, just change it. Justin's example uses a 24-hour clock, so you'd use `'05/23/2012 15:15:00'`. – Jeffrey Kemp Sep 18 '12 at 04:21
  • @Jeffrey Kemp, It does not work with '05/23/2012 15:15:00', It throws "ORA-01849: hour must be between 1 and 12". Justin's second solution works perfectly. – Kiranshell Sep 18 '12 at 04:38
  • @Kiranshell, yes, of course it won't work if you put 15 when your hour format specifies the 12-hour clock (`HH12`). Justin's original example, which I referred to, used the 24-hour clock (`HH24`). – Jeffrey Kemp Sep 18 '12 at 06:14