问题描述
我的数据库保存日期为这种格式 2015年5月29日12:07:58.000000 AM
。而我从用户输入的日期为 2015年5月29日12时07分58秒
格式。可我只是比较SQL命令的整条生产线的日/月/年呢?我的数据库是Oracle和格式TIMESTAMP。
任何人有任何想法怎么办呢?
No. Date/Timestamps doesn't have any format. Oracle doesn't store the date/timestamps in the format you see. The format you see is only for display purpose. Date is internally stored in 7 bytes which is Oracle's proprietary format.
Of course you could. You could use TRUNC which truncates the time portion and leaves only date portion and data type remains as date.
For example,
SQL> SELECT TRUNC(SYSTIMESTAMP) my_tmstmp FROM DUAL;
MY_TMSTMP
----------
2015-05-29
So, you get the user input in that format as a string. You need to first convert it into DATE using TO_DATE and then compare it.
For example,
WHERE TRUNC(dt_column) < TO_DATE('05/29/2015 12:07:58', 'MM/DD/YYYY HH24:MI:SS')
As I already said, if you want to ignore the time portion, then apply TRUNC.
However, applying TRUNC on the date column would suppress any regular index on that column. From performance point of view, better use a Date range condition.
For example,
WHERE dt_column
BETWEEN
TO_DATE('05/29/2015 12:07:58', 'MM/DD/YYYY HH24:MI:SS')
AND
TO_DATE('05/29/2015 12:07:58', 'MM/DD/YYYY HH24:MI:SS') +1
这篇关于比较SQL命令日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!