如何以"DD MON YYYY"格式显示DATE? [英] How do I display DATE in 'DD MON YYYY' format?

问题描述

我是Oracle数据库编程的新手，我希望以'DD MON YYYY'格式插入(也显示)日期. (PS:这仅涉及INSERT事件).为了完成这种格式，哪种数据类型(DATE或TIMESTAMP)最适合我?我应该怎么做?谢谢.

I am a newbie for Oracle database programming and I wish to INSERT date (also display) in 'DD MON YYYY' format. (PS: This only involves INSERT event). Which data type (DATE or TIMESTAMP) is the most suitable option for me in order to accomplish this format? How was I supposed to do that? Thanks.

推荐答案

DATE列没有任何格式.

因此，插入或更新数据时使用的格式与显示该数据无关(这就是为什么您永远不要在VARCHAR列中存储日期的原因之一).

So the format that you use when inserting or updating data is irrelevant for displaying that data (that's one of the reasons why you should never store a date in a VARCHAR column).

您在SQL工具(例如SQL * Plus)中看到的DATE列的任何格式化输出均由该工具应用.它不是该列中存储的数据的一部分.

Any formatted output you see for a DATE column in your SQL tool (e.g. SQL*Plus) is applied by that tool. It is not part of the data stored in that column.

在提供日期文字时，您应该将to_date()函数与显式格式掩码一起使用:

When providing a date literal you should either use the to_date() function with an explicit format mask:

insert into some_table (some_date_column)
values (to_date('27-06-2014', 'dd-mm-yyyy'));

在提供日期文字时，我也不建议使用带有书面月份名称的格式(2014年6月27日)，因为它们也取决于客户端计算机的NLS设置，并且可能会产生(随机)由于语言不同而产生的错误.仅使用数字更加健壮.

I also do not recommend using formats with written month names (27-JUN-2014) when supplying a date literal because they also depend on the NLS settings of the client computer and might produce (random) errors due to different languages. Using numbers only is much more robust.

我更喜欢使用ANSI日期文字，因为它的键入要少一些:

I prefer to use ANSI date literals because it's a bit less typing:

insert into some_table (some_date_column)
values (DATE '2014-06-27');

ANSI日期(或时间戳)文字的格式总是 ISO格式(yyyy-mm-dd).

The format for an ANSI date (or timestamp) literal is always the ISO format (yyyy-mm-dd).

当您选择数据时，您可以按照自己喜欢的格式显示日期.通过使用to_char()函数(例如，使用SQL工具)或使用编程语言中的函数(在应用程序内部使用的首选方式):

When you select your data you can display the date in whatever format you like. Either by using the to_char() function (e.g. when using a SQL tool) or using functions from your programming language (the preferred way to use inside an application):

select to_char(some_date_column,'dd-mon-yyyy')
from some_table;

请注意，Oracle中的DATE数据类型(尽管名称)也存储时间. TIMESTAMP仅以更高的精度(包括毫秒，而DATE数据类型仅存储秒)来执行相同的操作.

Note that the DATE data type in Oracle (despite it's name) also stores the time. a TIMESTAMP does the same thing only with a higher precision (it includes milliseconds, whereas the DATE data type only stores seconds).

这篇关于如何以"DD MON YYYY"格式显示DATE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！