sql⽇期格式转换函数_SQL转换⽇期函数和格式
⽇期格式转换函数
In this article, we will explore various SQL Date formats to use in writing SQL queries.
在本⽂中，我们将探索各种SQL转换⽇期格式，以⽤于编写SQL查询。
We need to work with date type data in SQL. It can be a complicated thing to deal with, at times, for SQL Server developers. Suppose you have a Product table with a column timestamp. It creates a timestamp for each customer order. You might face the following issues with it
我们需要使⽤SQL中的⽇期类型数据。 对于SQL Server开发⼈员⽽⾔，有时可能会很复杂。 假设您有⼀个带有列时间戳的Product表。 它为每个客户订单创建⼀个时间戳。 您可能会遇到以下问题
You fail to insert data in the Product table because the application tries to insert data in a different date format
您⽆法在“产品”表中插⼊数据，因为应⽤程序尝试以其他⽇期格式插⼊数据
Suppose you have data in a table in format YYYY-MM-DD hh:mm: ss. You have a daily Sales report, and in that, you want data group by date. You want to have data in the report in format YYYY-MM-DD
假设表中的数据格式为YYYY-MM-DD hh：mm：ss。 您有⼀个每⽇销售报告，在该报告中，您需要按⽇期分组数据。 您希望报表中的数据格式为YYYY-MM-DD
We do face many such scenarios when we do not have date as per our requirement. We cannot change table properties to satisfy each requirement. In this case, we need to use the built-in-functions in SQL Server to give the required date format.
当我们没有要求的⽇期格式时，我们确实会遇到许多这样的情况。 我们不能更改表属性来满⾜每个要求。 在这种情况下，我们需要使⽤SQL Server中的内置来提供所需的⽇期格式。
⽇期和时间的数据类型 (Data Types for Date and Time)
We have the following SQL convert date and Time data types in SQL Server.
在SQL Server中，我们具有以下SQL转换⽇期和时间数据类型。
Date type Format
Time hh:mm:ss[.nnnnnnn]
Date YYYY-MM-DD
SmallDateTime YYYY-MM-DD hh:mm:ss
DateTime YYYY-MM-DD hh:mm:ss[.nnn]
DateTime2YYYY-MM-DD hh:mm:ss[.nnnnnnn]
DateTimeOffset YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm
⽇期类型格式
时间hh：mm：ss [.nnnnnnn]
⽇期YYYY-MM-DD
SmallDateTime YYYY-MM-DD hh：mm：ss
约会时间YYYY-MM-DD hh：mm：ss [.nnn]
⽇期时间2YYYY-MM-DD hh：mm：ss [.nnnnnnn]
DateTimeOffset YYYY-MM-DD hh：mm：ss [.nnnnnnn] [+ |-] hh：mm
In SQL Server, we have used built-in functions such as SQL GETDATE() and GetUTCDate() to provide server date and format in various formats.在SQL Server中，我们使⽤了诸如SQL GETDATE（）和GetUTCDate（）之类的内置函数来提供各种格式的服务器⽇期和格式。SYSDATETIME(): To returns the server’s date and time SYSDATETIME（） ：返回服务器的⽇期和时间SYSDATETIMEOffset(): It returns the server’s date and time, along with UTC offset SYSDATETIMEOffset（） ：它返回服务器的⽇期和时间以及UTC偏移量GETUTCDATE(): It returns date and GMT (Greenwich Mean Time ) time GETUTCDATE（） ：返回⽇期和格林威治标准时间（格林威治标准时间）GETDATE(): It returns server date and time
GETDATE（） ：返回服务器⽇期和时间
Execute the following queries to get output in respective formats.
执⾏以下查询以获取相应格式的输出。
1.
2.
3.
SQL 转换⽇期格式 (SQL Convert Date Formats)
As highlighted earlier, we might need to format a date in different formats as per our requirements. W
e can use the SQL CONVERT() function in SQL Server to format DateTime in various formats.
如前所述，我们可能需要根据要求以不同的格式格式化⽇期。 我们可以在SQL Server中使⽤SQL CONVERT（）函数将DateTime格式化为各种格式。
Syntax for the SQ: CONVERT() function is as follows.
1Select SYSDATETIME() as [SYSDATETIME]
21Select SYSDATETIMEOffset() as [SYSDATETIMEOffset]
21Select GETUTCDATE() as [GETUTCDATE]
2Select GETDATE() as [GETDATE]
SQ： CONVERT（）函数的语法如下。
Data_Type: We need to define data type along with length. In the date function, we use Varchar(length) data types Data_Type：我们需要定义数据类型以及长度。 在⽇期函数中，我们使⽤Varchar（length）数据类型Date: We need to specify the date that we want to convert ⽇期 ：我们需要指定要转换的⽇期
DateFormatCode: We need to specify DateFormatCode ：我们需要指定DateFormatCode to convert a date in an appropriate form. We will explore more on this in the upcoming section DateFormatCode以适当的格式转换⽇期。 我们将在接下来的部分中进⼀步探讨
Let us explore various date formats using SQL convert date functions.
让我们使⽤SQL转换⽇期函数探索各种⽇期格式。
First, we declare a variable to hold current DateTime using the SQL GETDATE() function with the following query.
⾸先，我们使⽤SQL GETDATE（）函数通过以下查询声明⼀个变量，以保存当前的DateTime。
We can see various date formats in the following table. You can keep this table handy for reference purpose in the format of Date Time columns.
我们可以在下表中看到各种⽇期格式。 您可以⽅便地以“⽇期时间”列的格式将此表⽤于参考。Date an
d Time Formats SQL convert date query Output
Datetime format as MM/DD/YY Standard:
U.S.A.Datetime format in YY.MM.DD
formatStandard: ANSI Datetime format in DD/MM/YY format
Standard: British/French Datetime format in DD.MM.YY format
Standard: German SELECT CONVERT (data_type(length)),Date, DateFormatCode)
1
declare @Existingdate datetime 2
Set @Existingdate=GETDATE()3Print @Existingdate
1declare @Existingdate datetime
2Set @Existingdate=GETDATE()
3
Select CONVERT(varchar,@Existingdate,1) as [MM/DD/YY]1declare @Existingdate datetime
2Set @Existingdate=GETDATE()
3
Select CONVERT(varchar,@Existingdate,2) as [YY.MM.DD]1declare @Existingdate datetime
2Set @Existingdate=GETDATE()
3
Select CONVERT(varchar,@Existingdate,3) as [DD/MM/YY]1declare @Existingdate datetime
2Set @Existingdate=GETDATE()
3
Select CONVERT(varchar,@Existingdate,4) as [DD.MM.YY]1
declare @Existingdate datetime 2
Set @Existingdate=GETDATE()3Select CONVERT(varchar,@Existingdate,5) as [DD-MM-YY]
Datetime format in
DD-MM-YY format Standard: Italian Datetime format in DD MMM YY format
Standard: Shortened
month name Datetime format in
MMM DD, YY format
Standard: Shortened
month name Datetime Format
In HH:MM: SS
Standard: 24 hour time Datetime format as
[MMM DD YYYY
hh:mm:ss:mmm(AM/PM)]
Standard: Default +
milliseconds Datetime format in MM-DD-YY format
Standard: USA Datetime format in YY/MM/DD format
Standard: JAPAN Datetime format in YYMMDD format
Standard: ISO Datetime format in
DD MMM YYYY
HH:MM:SS:MMM
Standard: Europe default
+ milliseconds Datetime format in
HH:MM:SS:MMM
Standard:  24 hour time
with milliseconds 1declare @Existingdate datetime 2Set @Existingdate=GETDATE()3Select CONVERT(varchar,@Existingdate,6) as [DD MMM YY]
1declare @Existingdate datetime 2Set @Existingdate=GETDATE()3Select CONVERT(varchar,@Existingdate,7) as [MMM DD,YY]
1declare @Existingdate datetime 2Set @Existingdate=GETDATE()3Select CONVERT(varchar,@Existingdate,8) as [hh:mm:ss]
1declare @Existingdate datetime 2Set @Existingdate=GETDATE()3Select CONVERT(varchar,@Existingdate,9) as [MMM DD YYYY hh:mm:ss:mmm(AM/PM)]
1declare @Existingdate datetime
2Set @Existingdate=GETDATE()
3
Select CONVERT(varchar,@Existingdate,10) as [MM-DD-YY]1declare @Existingdate datetime
2Set @Existingdate=GETDATE()
3
Select CONVERT(varchar,@Existingdate,11) as [YY/MM/DD]1declare @Existingdate datetime
2Set @Existingdate=GETDATE()
3
Select CONVERT(varchar,@Existingdate,12) as [YYMMDD]1declare @Existingdate datetime 2Set @Existingdate=GETDATE()3Select CONVERT(varchar,@Existingdate,13) as [DD MMM YYYY HH:MM:SS:MMM]
unix时间戳转换日期格式1declare @Existingdate datetime 2Set @Existingdate=GETDATE()3Select CONVERT(varchar,@Existingdate,14) as [HH:MM:SS:MMM]
Datetime format in
YYYY-MM-DD HH:MM:SS
Default: ODBC canonical Datetime format in
YYYY-MM-DD
HH:
Standard: ODBC
canonical with
milliseconds
Datetime format in
mm/dd/yy hh:mm:ss
(AM/PM) Standard: USA
with Time AM/PM
Datetime format in [yyyy-mm-dd]
Datetime format in [hh:mm:ss]
Datetime format in
[mm-dd-yyyy
hh:]
Datetime format in [MMM DD YYYY HH: SS (AM/PM)] Standard: Default
Datetime format in
[MM/DD/YYYY] Standard: USA
Datetime format in [YYYY.MM.DD] Standard: ANSI
Datetime format in
DD/MM/YYYY format Standard: British/French 1declare @Existingdate datetime
2Set @Existingdate=GETDATE()
3Select CONVERT(varchar,@Existingdate,20) as [YYYY-MM-DD HH:MM:SS]
1declare @Existingdate datetime
2Set @Existingdate=GETDATE()
3Select CONVERT(varchar,@Existingdate,21) as [YYYY-MM-DD HH:]
1declare @Existingdate datetime
2Set @Existingdate=GETDATE()
3Select CONVERT(varchar,@Existingdate,22) as [mm/dd/yy hh:mm:ss (AM/PM)]
1declare @Existingdate datetime
2Set @Existingdate=GETDATE()
3Select CONVERT(varchar,@Existingdate,23) as [yyyy-mm-dd]
1declare @Existingdate datetime
2Set @Existingdate=GETDATE()
3Select CONVERT(varchar,@Existingdate,24) as [hh:mm:ss]
1declare @Existingdate datetime
2Set @Existingdate=GETDATE()
3Select CONVERT(varchar,@Existingdate,27) as [mm-dd-yyyy hh:]
1declare @Existingdate datetime
2Set @Existingdate=GETDATE()
3Select CONVERT(varchar,@Existingdate,100) as [MMM DD YYYY HH:SS (AM/PM)]
1declare @Existingdate datetime
2Set @Existingdate=GETDATE()
3Select CONVERT(varchar,@Existingdate,101) as [MM/DD/YYYY]
4
1declare @Existingdate datetime
2Set @Existingdate=GETDATE()
3Select CONVERT(varchar,@Existingdate,102) as [YYYY.MM.DD]
1declare @Existingdate datetime
2Set @Existingdate=GETDATE()
3Select CONVERT(varchar,@Existingdate,103) as [DD/MM/YYYY]

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。