如何在 oracle 中以 dd/mm/yyyy 格式将当前日期插入 DATE 字段
我的表中有一个字段，Oracle 中的数据类型为 DATE.我想以 DD/MM/YYYY 格式将当前日期插入该字段.
I have a field in my table with datatype as DATE in Oracle. I want to insert the current date into that field, in format DD/MM/YYYY format.
我尝试了以下查询:
select to_date(to_char(sysdate,'dd/mm/yyyy'),'dd/mm/yyyy') from dual
但它给了
1/8/2011 12:00:00 AM.
我希望它插入并显示为
08/01/2011 12:00:00 AM.
有人可以帮我吗?
推荐答案
DATE是Oracle的内置类型，以固定的方式表示，你无法控制.
DATE is a built-in type in Oracle, which is represented in a fixed way and you have no control over it.
所以:
我希望它插入 [...] 作为 08/01/2011 12:00:00 AM
I want it to insert [...] as 08/01/2011 12:00:00 AM
以上是胡说八道.您不插入字符串，而是插入日期.
The above is nonsensical. You don't insert a string, you insert a date.
格式仅在您需要时才有用:
Format is useful only when you want:
- 使用
TO_DATE将字符串转换为日期的内部表示(格式掩码:如何解析字符串)； - 使用
TO_CHAR将日期的内部表示转换为字符串(格式掩码:如何呈现日期).
- to convert a string to an internal representation of date with
TO_DATE(format mask: how to parse the string); - to convert an internal representation of date to a string with
TO_CHAR(format mask: how to render the date).
因此，基本上，在您的示例中，您采用 DATE，将其转换为具有某种格式的 STRING，然后将其转换回具有相同格式的 DATE.这是无操作的.
So basically, in your example you take a DATE, you convert it to a STRING with some format, and convert it back to DATE with the same format. This is a no-op.
现在，您的客户端显示的内容:这是因为您的 Oracle 客户端不会直接显示 DATE 字段，而 NLS 层将转换任何选定的 DATE 字段.所以默认情况下它取决于您的语言环境.
Now, what your client displays: this is because your Oracle Client won't display DATE fields directly and the NLS layer will convert any DATE field that is selected. So it depends on your locale by default.
你想要的是 SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') FROM dual; 它将显式执行转换并返回一个字符串.
What you want is SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') FROM dual; which will explicitly perform the conversion and return a string.
当您想在数据库中插入日期时，您可以使用 TO_DATE 或日期文字.
And when you want to insert a date in a database, you can use TO_DATE or date literals.
相关文章