请教oracle 10g 关于日期格式的问题

Question

要实现一个根据日期来查询：
insert表中的时候日期字典用的sysdate
但是插入的数据中间有中文，比如：2010-4-26 上午 20:22:22这样的话，查询出来的这个字段和前天传过来要查询的日期就不能用"="来匹配用的数据库是服务器上的的，在别人的机器上登录同样的库，查询出的结果没有中间的“上午”请问是什么原因？

Answer 1

日期格式的显示问题
执行查询之前 先执行
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
就好了

Answer 2

插入数据的时候就转换成'yyyy-mm-dd hh24:mi:ss'

Answer 3

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select sysdate from dual;

Answer 4

select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual 

Answer 5

WORKGROUP\DINGJUN-PC>select sysdate from dual;SYSDATE
--------------
27-4月 -10WORKGROUP\DINGJUN-PC> select * from nls_session_parameters;PARAMETER
                                                                                 VALUE
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------ -----------------------
------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE
                                                                                 SIMPLIFIED CHINESE
NLS_TERRITORY
                                                                                 CHINA
NLS_CURRENCY
                                                                                 ￥
NLS_ISO_CURRENCY
                                                                                 CHINA
NLS_NUMERIC_CHARACTERS
                                                                                 .,
NLS_CALENDAR
                                                                                 GREGORIAN
NLS_DATE_FORMAT
                                                                                 DD-MON-RR
NLS_DATE_LANGUAGE
                                                                                 SIMPLIFIED CHINESE
NLS_SORT
                                                                                 BINARY
NLS_TIME_FORMAT
                                                                                 HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT
                                                                                 DD-MON-RR HH.MI.SSXFF AMPARAMETER
                                                                                 VALUE
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------ -----------------------
------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
NLS_TIME_TZ_FORMAT
                                                                                 HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT
                                                                                 DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY
                                                                                 ￥
NLS_COMP
                                                                                 BINARY
NLS_LENGTH_SEMANTICS
                                                                                 BYTE
NLS_NCHAR_CONV_EXCP
                                                                                 FALSE已选择17行。WORKGROUP\DINGJUN-PC>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';会话已更改。WORKGROUP\DINGJUN-PC>select sysdate from dual;SYSDATE
-------------------
2010-04-27 08:56:29WORKGROUP\DINGJUN-PC>select to_char(sysdate,'yyyy-mon-dd') from dual;TO_CHAR(SYSDATE,'YYYY-MON-DD')
--------------------------------
2010-4月 -27WORKGROUP\DINGJUN-PC>select to_char(sysdate,'yyyy-mm-dd') from dual;TO_CHAR(SYSDATE,'YYY
--------------------
2010-04-27WORKGROUP\DINGJUN-PC>alter session set nls_date_language='American';会话已更改。WORKGROUP\DINGJUN-PC>select to_char(sysdate,'yyyy-mon-dd') from dual;TO_CHAR(SYSDATE,'YYYY-MON-DD')
----------------------------------------
2010-apr-27

Answer 6

还是不太明白在pl/sql里执行alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';select to_date('2009-02-08 08:22:22' ,'yyyy-mm-dd hh24:mi:ss') from dual;查询的结果还是
2009-2-8 上午 08:22:22
在线等~~

Answer 7

那是plsql的问题，设置首选项 ---用户界面----日期时间

Answer 8

to_char(sysdate,'yyyy-mm-dd hh:mi:ss')可以转换，或者设置时间显示格式

Answer 9

因为当时如果用sysdate插入的，则数据库中存储的日期可能精确到了毫秒或者微秒级别，所以无法用=，最好转换完成您需要的时间精度再进行比较，比如年月日则可以比较某一天的。

Answer 10

date类型只到秒
LZ自己的提问就有问题