SQL 使用日期在 Oracle 中创建表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13361040/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
creating table in Oracle with Date
提问by user182944
I want to create a table in Oracle 10g and I want to specify the date format for my date column. If I use the below syntax:
我想在 Oracle 10g 中创建一个表，并且我想为我的日期列指定日期格式。如果我使用以下语法：
create table datetest(
........
startdate date);
Then the date column will accept the date format DD-MON-YYwhich I dont want.
然后日期列将接受DD-MON-YY我不想要的日期格式。
I want the syntax for my date column to be MM-DD-YYYY
我希望我的日期列的语法是 MM-DD-YYYY
Please let me know how to proceed with this.
请让我知道如何进行此操作。
Regards,
问候，
回答by Dave Costa
A DATEhas no inherent format. It is not simply a string that happens to represent a date. Oracle has its own internal format for storing date values.
ADATE没有固有格式。它不仅仅是一个恰好代表日期的字符串。Oracle 有自己的内部格式来存储日期值。
Formats come into play when actual date values need to be converted into strings or vice versa, which of course happens a lot since interactively we write dates out as strings.
当实际日期值需要转换为字符串时，格式就会发挥作用，反之亦然，这当然经常发生，因为我们以交互方式将日期写为字符串。
The default date format for your database is determined by the settings NLS_DATE_FORMAT, which you probably have set to DD-MON-YYYY(which I believe is the default setting for American English locales). You can change this at the database level or for a single session for convenience, but in general it is safer programming practice to be explicit so that you don't get errors or, worse, wrong results if your code is run in a different environment.
数据库的默认日期格式由 settings 决定NLS_DATE_FORMAT，您可能已设置为DD-MON-YYYY（我认为这是美国英语语言环境的默认设置）。为方便起见，您可以在数据库级别或单个会话中更改此设置，但一般而言，明确的编程实践更安全，这样您就不会出现错误，或者更糟的是，如果您的代码在不同的环境中运行，则不会出现错误的结果.
The simplest way to specify a date value unambiguously is a date literal, which is the word 'date' followed by a string representing the date in YYYY-MM-DD format, e.g. date '2012-11-13'. The Oracle parser directly translates this into the corresponding internal date value.
明确指定日期值的最简单方法是日期文字，即“日期”一词后跟表示 YYYY-MM-DD 格式的日期的字符串，例如date '2012-11-13'. Oracle 解析器直接将其转换为相应的内部日期值。
If you want to use a different format, then I recommend explicitly using TO_CHAR/TO_DATE with your desired format model in your code. Examples:
如果您想使用不同的格式，那么我建议在代码中明确使用 TO_CHAR/TO_DATE 和所需的格式模型。例子：
INSERT INTO my_table (my_date) VALUES ( TO_DATE( '11-13-2012', 'MM-DD-YYYY' ) );
SELECT TO_CHAR( my_date, 'MM-DD-YYYY' ) FROM my_table;
回答by DazzaL
dates rdo not have a format like you're suggesting. they are stored internally as a 7 byte number. to format the date when selecting, please use TO_CHAR(yourdatefield, 'format')
日期 r 没有您建议的格式。它们在内部存储为一个 7 字节的数字。选择时格式化日期，请使用TO_CHAR(yourdatefield, 'format')
where formats are all shown here: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924
格式都在这里显示：http: //docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924
eg to_char(startdate, 'mm-dd-yyyy')
