search date and time in oracle using to

 

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. 在这里输入图像描述

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' )
链接地址: http://www.djcxy.com/p/6298.html

上一篇: Oracle中的00600异常

下一篇: 搜索日期和时间在oracle中使用