SQL：如何在创建表时指定日期格式并填充它
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/47178740/
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
SQL: how to specify date format on creating table and fill it
提问by Mikhail_Sam
I want to save date in format 'dd.mm.yyyy'. So I read there are different formats for date in SQL (by the way I use Visual Studio and SQL Server).
我想以“dd.mm.yyyy”格式保存日期。所以我读到 SQL 中有不同的日期格式（通过我使用 Visual Studio 和 SQL Server 的方式）。
I tried this code:
CREATE TABLE APP(
ID INT NOT NULL,
DT DATE FORMAT 'dd.mm.yyyy',
ADDRESS NVARCHAR (100) ,
PRIMARY KEY (ID)
);
But it returns the error:
但它返回错误：
Incorrect syntax near 'FORMAT'.
“FORMAT”附近的语法不正确。
After that I want to use this code:
之后我想使用这个代码：
INSERT INTO APP (ID, DT)
VALUES ('1','22.12.2016')
回答by Gordon Linoff
Dates are stored in an internal format. Formats only make sense for input and output.
日期以内部格式存储。格式只对输入和输出有意义。
In your case you want the date in a German format (104), so you can use:
在您的情况下，您需要德语格式（104）的日期，因此您可以使用：
select convert(varchar(255), dt, 104)
If you like, you can include the formatted date as a separate column:
如果您愿意，可以将格式化日期作为单独的列包括在内：
CREATE TABLE APP (
ID INT NOT NULL,
DT DATE,
ADDRESS NVARCHAR(100),
DT_FORMATTED AS (convert(varchar(255), dt, 104)),
PRIMARY KEY (ID)
);
You can then refer to dt_formattedto get the string in the format you want.
然后您可以参考以dt_formatted获取您想要的格式的字符串。
回答by Martin Brown
You don't need to specify the format in the table definition as dates are stored in a binary format.
您不需要在表定义中指定格式，因为日期以二进制格式存储。
CREATE TABLE APP(
ID INT NOT NULL,
DT DATE,
ADDRESS NVARCHAR (100) ,
PRIMARY KEY (ID)
);
When you try to insert into that table however, the server will try to convert the string to a date before inserting it. This can be problematic as it is unable to tell if 12.11.2017 is the 12th of November or 11th of December. To figure this out it uses the localization settings of the user account that is performing the operation.
但是，当您尝试插入该表时，服务器将尝试在插入之前将字符串转换为日期。这可能会有问题，因为它无法判断 12.11.2017 是 11 月 12 日还是 12 月 11 日。为了解决这个问题，它使用了执行操作的用户帐户的本地化设置。
Often you will find that the account that is running the operation is set to USA format, month day then year (MDY), when what you want is day month year (DMY) format. One way to tell it what the sequence of the date's parts is to use the DATEFORMATsetting like this:
通常您会发现运行操作的帐户设置为美国格式，月日然后年 (MDY)，而您想要的是日月年 (DMY) 格式。告诉它日期部分的顺序的一种方法是使用DATEFORMAT设置，如下所示：
SET DATEFORMAT dmy;
INSERT INTO APP (ID, DT)
VALUES (1,'22.12.2016')
Another alternative is to cast the string to a date using the CONVERTfunction and tell it what the date format is. The formats have numeric codeslike 104 for German format Like this:
另一种替代方法是使用CONVERT函数将字符串转换为日期并告诉它日期格式是什么。格式有数字代码，如德语格式的 104 像这样：
INSERT INTO APP (ID, DT)
VALUES (2,CONVERT(date,'22.12.2016',104))
回答by Prasan Karunarathna
Dates are stored in an internal format. Formats only make sense for input and output.
日期以内部格式存储。格式只对输入和输出有意义。
You can include the formatted date as a separate column:
您可以将格式化日期作为单独的列包括在内：
SQL Server supports the date format, You have to use the below date format
SQL Server 支持日期格式，您必须使用以下日期格式
With century (yyyy) | Standard | Input/Output
与世纪 (yyyy) | 标准 | 输入输出
103 | British/French | 103 = dd/mm/yyyy
CREATE TABLE [dbo].[Post]
(
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] VARCHAR(MAX) NULL,
[RowNo] INT NULL,
[ColNo] INT NULL,
[Deadline] (CONVERT(VARCHAR(255), dt, 103)), -- Include the formatted date as a separate column
CONSTRAINT [PK_KtoCo]
PRIMARY KEY CLUSTERED ([Id] ASC)
);
回答by pankaj singh
use this
用这个
CREATE TABLE APP(
ID INT NOT NULL,
DT DATE ,
ADDRESS NVARCHAR (100) ,
PRIMARY KEY (ID)
);
回答by Rohit yadav
its default setting is yyyy-MM-dd
它的默认设置是 yyyy-MM-dd
No, it's not. There is no formatting information at all associated with the field.
不，这不对。根本没有与该字段相关联的格式信息。
The value is not formatted by the database, it's returned only as a point in time. Formatting that value into it's textual representation is done by the applcation that is getting the data from the database.
该值不是由数据库格式化的，它仅作为一个时间点返回。将该值格式化为它的文本表示是由从数据库中获取数据的应用程序完成的。
So, there is nothing that you can do in the database to change how the date value is formatted, you have to change that where the data is displayed.
因此，您无法在数据库中执行任何操作来更改日期值的格式，您必须更改数据的显示位置。
回答by Harshavardhan Kalsait
Use this.
用这个。
CREATE TABLE:
创建表：
CREATE TABLE EMP
(EID NUMBER(20),
ENAME VARCHAR2(20),
DT DATE,
SAL NUMBER(20));
INSERT INTO THE TABLE:
插入表格：
INSERT INTO EMP (EID,ENAME,DT,SAL) VALUES(01,'ABCDE','11.NOV.2011',10000);
O/P OF ABOVE TABLE:
上表的 O/P：
SELECT * FROM EMP;
EID ENAME DT SAL
EID ENAME DT SAL
01 ABCDE 11-DEC-11 10000
01 ABCDE 11-DEC-11 10000
