如何在Oracle 10g中将日期更改为时间 [英] how to change the date to time in oracle 10g

问题描述

我必须使用TO_DATE函数在插入时输入STIMING的时间，但是它给我的日期不是时间，应该是时间.

I have to put in STIMING a time when I insert I use TO_DATE function but it give me date not time and it should be time.

这是我使用的表格和代码

This is the table and the code that i use

SQL>从shift选择*;

SQL> select * from shift;

       SNO SNAME                STIMING
---------- -------------------- ---------
    121323 morning              01-APR-17
    112232 evening              01-APR-17
    665342 afternoon            01-APR-17

SQL> update shift
2  set  STIMING= ('07:00:00 HH,MI,SS') 
3  where SNO=121323;
set  STIMING= ('07:00:00 HH,MI,SS')             
 *
ERROR at line 2:
ORA-01843: not a valid month

推荐答案

我必须投入STIMING时间

Oracle没有TIME数据类型. DATE数据类型始终在内部存储为 7字节，并且始终由年份(2字节)和月，日，小时，分钟和秒(每个1字节).

Oracle does not have a TIME datatype. The DATE data type is always stored internally as 7-bytes and is always composed of year (2-bytes) and month, day, hours, minutes and seconds (1-byte each).

您不能没有DATE的年，月或日组成部分.

You cannot not have a year, month or day component of a DATE.

如果您希望自己有时间，则必须将其存储为其他数据类型或存储年/月/日，而忽略该部分.

If you want a time on its own then you will have to store it as a different data type or store the year/month/day and ignore that component.

在SELECT列中的STIMING列中，没有显示时间分量.您可以通过更改NLS_DATE_FORMAT会话参数中设置的默认日期格式来更改此设置.

When you are SELECTing the STIMING column it is not showing the time component. You can change this by changing the default date format which is set in the NLS_DATE_FORMAT session parameter.

您可以使用以下方法查看此参数:

You can review this parameter using:

SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';

您可以使用以下方法在当前会话中设置此值:

You can set this value within your current session using:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

(注意:这不会更改任何其他用户的值.)

插入日期时，您可以使用:

When you insert the date you can use:

INSERT INTO shift ( SNO, SNAME, STIMING)
VALUES ( 121323, 'morning', TO_DATE( '01-APR-2017 07:00' DD-MON-YYYY HH24:MI' ) )

或者是ANSI TIMESTAMP文字(将隐式转换为列的DATE格式):

Or, an ANSI TIMESTAMP literal (which will be implicitly cast to the DATE format of the column):

INSERT INTO shift ( SNO, SNAME, STIMING)
VALUES ( 121323, 'morning', TIMESTAMP '2017-04-01 07:00:00' )

这篇关于如何在Oracle 10g中将日期更改为时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！