sql日期格式转换函数_SQL转换日期函数和格式

最新推荐文章于 2024-09-20 15:45:19 发布

culuo4781

最新推荐文章于 2024-09-20 15:45:19 发布

阅读量1.1w

点赞数 1

文章标签： java python sql 大数据 mysql

原文链接：https://www.sqlshack.com/sql-convert-date-functions-and-formats/

版权

sql日期格式转换函数

In this article, we will explore various SQL Convert 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 format 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]
DateTime2	YYYY-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]
日期时间2	YYYY-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.

执行以下查询以获取相应格式的输出。

```
Select SYSDATETIME() as [SYSDATETIME]
```

Select SYSDATETIMEOffset() as [SYSDATETIMEOffset]

```
Select GETUTCDATE() as [GETUTCDATE]
```
```
Select GETDATE() as [GETDATE]
```

SQL转换日期格式 (SQL Convert Date Formats)

As highlighted earlier, we might need to format a date in different formats as per our requirements. We 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.

SQ： CONVERT（）函数的语法如下。

SELECT CONVERT (data_type(length)),Date, DateFormatCode)

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。

declare @Existingdate datetime
Set @Existingdate=GETDATE()
Print @Existingdate

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 and Time Formats	SQL convert date query	Output
Datetime format as MM/DD/YY Standard: U.S.A.	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,1) as [MM/DD/YY]`
Datetime format in YY.MM.DD format Standard: ANSI	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,2) as [YY.MM.DD]`
Datetime format in DD/MM/YY format Standard: British/French	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,3) as [DD/MM/YY]`
Datetime format in DD.MM.YY format Standard: German	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,4) as [DD.MM.YY]`
Datetime format in DD-MM-YY format Standard: Italian	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,5) as [DD-MM-YY]`
Datetime format in DD MMM YY format Standard: Shortened month name	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,6) as [DD MMM YY]`
Datetime format in MMM DD, YY format Standard: Shortened month name	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,7) as [MMM DD,YY]`
Datetime Format In HH:MM: SS Standard: 24 hour time	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,8) as [hh:mm:ss]`
Datetime format as [MMM DD YYYY hh:mm:ss:mmm(AM/PM)] Standard: Default + milliseconds	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,9) as [MMM DD YYYY hh:mm:ss:mmm(AM/PM)]`
Datetime format in MM-DD-YY format Standard: USA	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,10) as [MM-DD-YY]`
Datetime format in YY/MM/DD format Standard: JAPAN	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,11) as [YY/MM/DD]`
Datetime format in YYMMDD format Standard: ISO	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,12) as [YYMMDD]`
Datetime format in DD MMM YYYY HH:MM:SS:MMM Standard: Europe default + milliseconds	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,13) as [DD MMM YYYY HH:MM:SS:MMM]`
Datetime format in HH:MM:SS:MMM Standard: 24 hour time with milliseconds	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,14) as [HH:MM:SS:MMM]`
Datetime format in YYYY-MM-DD HH:MM:SS Default: ODBC canonical	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,20) as [YYYY-MM-DD HH:MM:SS]`
Datetime format in YYYY-MM-DD HH:MM:SS.mmm Standard: ODBC canonical with milliseconds	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,21) as [YYYY-MM-DD HH:MM:SS.mmm]`
Datetime format in mm/dd/yy hh:mm:ss (AM/PM) Standard: USA with Time AM/PM	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,22) as [mm/dd/yy hh:mm:ss (AM/PM)]`
Datetime format in [yyyy-mm-dd]	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,23) as [yyyy-mm-dd]`
Datetime format in [hh:mm:ss]	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,24) as [hh:mm:ss]`
Datetime format in [mm-dd-yyyy hh:mm:ss.mmm]	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,27) as [mm-dd-yyyy hh:mm:ss.mmm]`
Datetime format in [MMM DD YYYY HH: SS (AM/PM)] Standard: Default	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,100) as [MMM DD YYYY HH:SS (AM/PM)]`
Datetime format in [MM/DD/YYYY] Standard: USA	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,101) as [MM/DD/YYYY]`
Datetime format in [YYYY.MM.DD] Standard: ANSI	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,102) as [YYYY.MM.DD]`
Datetime format in DD/MM/YYYY format Standard: British/French	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,103) as [DD/MM/YYYY]`
Datetime format in DD.MM.YY format Standard: German	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,104) as [DD/MM/YYYY]`
Datetime format in DD-MM-YY format Standard: Italian	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,105) as [DD/MM/YYYY]`
Datetime format in DD MMM YYYY format Standard: Shortened month name	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,106) as [DD MMM YYYY]`
Datetime format in MMM DD,YYYY format Standard: Shortened month name	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,107) as [MMM DD,YYYY]`
Datetime Format In HH:MM: SS Standard: 24 hour time	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,108) as [HH:MM:SS]`
Datetime format as [MMM DD YYYY hh:mm:ss:mmm(AM/PM)] Standard: Default + milliseconds	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,109) as [MMM DD YYYY hh:mm:ss:mmm(AM/PM)]`
Datetime format in MM- DD-YY format Standard: USA	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,110) as [MM-DD-YYYY]`
Datetime format in YYYY/MM/DD format Standard: JAPAN	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,111) as [YYYY/MM/DD]`
Datetime format in YYYYMMDD format Standard: ISO	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,112) as [YYYYMMDD]`
Datetime format in DD MMM YYYY HH:MM:SS: MMM Standard: Europe default + milliseconds	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,113) as [DD MMM YYYY HH:MM:SS:MMM]`
Datetime format in HH:MM:SS: MMM Standard: 24 hour time with milliseconds	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,114) as [DD MMM YYYY HH:MM:SS:MMM]`
Datetime format in YYYY-MM-DD HH:MM: SS Default: ODBC canonical	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,120) as [YYYY-MM-DD HH:MM:SS]`
Datetime format in YYYY-MM-DD HH:MM: SS.mmm Standard: ODBC canonical with milliseconds	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,121) as [YYYY-MM-DD HH:MM:SS.mmm]`
Datetime format in YYYY-MM-DDTHH:MM: SS.mmm Standard: ISO8601	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,126) as [yyyy-mm-ddThh:mi:ss.mmm]`
Datetime format in [DD MMM YYYY hh:mi:ss:mmm(AM/PM)] Standard: Islamic/Hijri date	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,130) as [dd mon yyyy hh:mi:ss:mmm(AM/PM)]`

日期和时间格式	SQL转换日期查询	输出量
日期时间格式为 MM / DD / YY 标准：美国	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,1) as [MM/DD/YY]`
日期时间格式 YY.MM.DD格式标准：ANSI	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,2) as [YY.MM.DD]`
日期时间格式 DD / MM / YY格式标准：英式/法式	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,3) as [DD/MM/YY]`
日期时间格式为DD.MM.YY 标准：德国	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,4) as [DD.MM.YY]`
日期时间格式 DD-MM-YY格式标准：意大利语	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,5) as [DD-MM-YY]`
日期时间格式 DD MMM YY格式标准：缩短月份名称	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,6) as [DD MMM YY]`
日期时间格式 MMM DD，YY格式标准：缩短月份名称	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,7) as [MMM DD,YY]`
日期时间格式在HH：MM：SS 标准时间：24小时	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,8) as [hh:mm:ss]`
日期时间格式为 [MMM DD YYYY hh：mm：ss：mmm（AM / PM）] 标准：默认+毫秒	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,9) as [MMM DD YYYY hh:mm:ss:mmm(AM/PM)]`
日期时间格式 MM-DD-YY格式标准：美国	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,10) as [MM-DD-YY]`
日期时间格式 YY / MM / DD格式标准：日本	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,11) as [YY/MM/DD]`
日期时间格式 YYMMDD格式标准：ISO	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,12) as [YYMMDD]`
日期时间格式 DD MMM YYYY HH：MM：SS：MMM 标准：欧洲默认值+毫秒	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,13) as [DD MMM YYYY HH:MM:SS:MMM]`
日期时间格式 HH：MM：SS：MMM 标准： 24小时制，以毫秒为单位	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,14) as [HH:MM:SS:MMM]`
日期时间格式 YYYY-MM-DD HH：MM：SS 默认值： ODBC规范	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,20) as [YYYY-MM-DD HH:MM:SS]`
日期时间格式 YYYY-MM-DD HH：MM：SS.mmm 标准：ODBC规范（以毫秒为单位）	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,21) as [YYYY-MM-DD HH:MM:SS.mmm]`
日期时间格式 mm / dd / yy hh：mm：ss（AM / PM）标准：美国时间为AM / PM	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,22) as [mm/dd/yy hh:mm:ss (AM/PM)]`
日期时间格式 [yyyy-mm-dd]	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,23) as [yyyy-mm-dd]`
日期时间格式 [hh：mm：ss]	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,24) as [hh:mm:ss]`
日期时间格式 [mm-dd-yyyy hh：mm：ss.mmm]	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,27) as [mm-dd-yyyy hh:mm:ss.mmm]`
日期时间格式 [MMM DD YYYY HH：SS（AM / PM）] 标准：默认	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,100) as [MMM DD YYYY HH:SS (AM/PM)]`
日期时间格式 [MM / DD / YYYY] 标准：美国	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,101) as [MM/DD/YYYY]`
日期时间格式 [YYYY.MM.DD] 标准：ANSI	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,102) as [YYYY.MM.DD]`
日期时间格式 DD / MM / YYYY格式标准：英式/法式	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,103) as [DD/MM/YYYY]`
日期时间格式 DD.MM.YY格式标准：德国	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,104) as [DD/MM/YYYY]`
日期时间格式 DD-MM-YY格式标准：意大利语	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,105) as [DD/MM/YYYY]`
日期时间格式 DD MMM YYYY格式标准：缩短月份名称	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,106) as [DD MMM YYYY]`
日期时间格式 MMM DD，YYYY格式标准：缩短月份名称	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,107) as [MMM DD,YYYY]`
日期时间格式在HH：MM：SS 标准时间：24小时	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,108) as [HH:MM:SS]`
日期时间格式为 [MMM DD YYYY hh：mm：ss：mmm（AM / PM）] 标准：默认+毫秒	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,109) as [MMM DD YYYY hh:mm:ss:mmm(AM/PM)]`
日期时间格式 MM- DD-YY格式标准：美国	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,110) as [MM-DD-YYYY]`
日期时间格式 YYYY / MM / DD格式标准：日本	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,111) as [YYYY/MM/DD]`
日期时间格式 YYYYMMDD格式标准：ISO	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,112) as [YYYYMMDD]`
日期时间格式 DD MMM YYYY HH：MM：SS：MMM 标准：欧洲默认值+毫秒	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,113) as [DD MMM YYYY HH:MM:SS:MMM]`
日期时间格式 HH：MM：SS：MMM 标准： 24小时制，以毫秒为单位	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,114) as [DD MMM YYYY HH:MM:SS:MMM]`
日期时间格式 YYYY-MM-DD HH：MM：SS 默认值： ODBC规范	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,120) as [YYYY-MM-DD HH:MM:SS]`
日期时间格式 YYYY-MM-DD HH：MM：SS.mmm 标准： ODBC规范（以毫秒为单位）	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,121) as [YYYY-MM-DD HH:MM:SS.mmm]`
日期时间格式 YYYY-MM-DDTHH：MM：SS.mmm 标准：ISO8601	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,126) as [yyyy-mm-ddThh:mi:ss.mmm]`
日期时间格式 [DD MMM YYYY hh：mi：ss：mmm（AM / PM）] 标准：伊斯兰教/回历	`declare @Existingdate datetime Set @Existingdate=GETDATE() Select CONVERT(varchar,@Existingdate,130) as [dd mon yyyy hh:mi:ss:mmm(AM/PM)]`

In the table, we can see various formats to SQL convert date as per your requirements. In the following table, you can see all SQL date formats together.

在表中，我们可以根据您的要求看到各种格式SQL转换日期。在下表中，您可以一起查看所有SQL日期格式。

Let us next explore a function that is useful for SQL convert date.

接下来让我们探讨一个对SQL转换日期有用的函数。

日期 (DATEADD)

We can use the SQL DATEADD function to add a particular period to our date. Suppose we have a requirement to add 1 month to current date. We can use the SQL DATEADD function to do this task.

我们可以使用SQL DATEADD函数将一个特定的时期添加到我们的日期中。假设我们有一个要求将1个月添加到当前日期。我们可以使用SQL DATEADD函数来完成此任务。

The syntax for SQL DATEADD function is as following

SQL DATEADD函数的语法如下

DATEADD(interval, number, date)

Interval: We can specify an interval that needs to be added in the specified date. We can have values such as year, quarter, month, day, week, hour, minute etc.

间隔：我们可以指定一个需要在指定日期添加的间隔。我们可以使用诸如年，季度，月，日，星期，小时，分钟等值。

Number: It specifies the number of the interval to add. For example, if we have specified interval as Month and Number as 2, it means 2 months needs to be added in date.

Number：指定要添加的时间间隔的编号。例如，如果我们将间隔指定为“月”并将数字指定为2，则意味着需要在日期中添加2个月。

In the following query, we want to add 2 months in the current date.

在以下查询中，我们要在当前日期添加2个月。

SELECT GETDATE() as Currentdate
 
SELECT DATEADD(month, 2, GETDATE()) AS NewDate;

You can see the output in the following screenshot.

您可以在以下屏幕截图中看到输出。

DateADD function SQL - - SQL Convert Date

Similarly, lets us add 1 year to current date using the following query.

同样，让我们使用以下查询将1年添加到当前日期。

select GETDATE() as Currentdate
 
SELECT DATEADD(Year, 1, GETDATE()) AS NewDate;

We can combine the SQL DATEADD and CONVERT functions to get output in desired DateTime formats. Suppose, in the previous example; we want a date format in of MMM DD, YYYY. We can use the format code 107 to get output in this format.

我们可以结合使用SQL DATEADD和CONVERT函数以所需的DateTime格式获取输出。假设在前面的示例中；我们想要的日期格式为MMM DD，YYYY。 我们可以使用格式代码107获得此格式的输出。

Execute the following code to get New date and ConvertedDate.

执行以下代码以获取新日期和ConvertedDate。

SELECT 
 DATEADD(YEAR,1,GETDATE()) AS [NewDate]
 ,CONVERT(varchar(110),DATEADD(YEAR,1,GETDATE()),107) AS [ConvertedDate]

结论 (Conclusion)

In this article, we explored various SQL convert date formats. It allows getting a date in required format with Covert function easily. You can use this article to take a reference for all date formats and use in your queries.

在本文中，我们探讨了各种SQL转换日期格式。通过隐蔽功能，可以轻松获取所需格式的日期。您可以使用本文作为所有日期格式的参考，并在查询中使用。