有关SQL Server中日期的常见问题解答

介绍 (Introduction)

In this article, I compiled a list of FAQs and Answers about dates.

在本文中，我整理了有关日期的常见问题解答列表。

1. Which function should I use to get the current date in SQL Server?
   我应该使用哪个函数来获取SQL Server中的当前日期？
2. How can I get the current time in the format hh:mm:ss?
   如何以hh：mm：ss格式获取当前时间？
3. How can I calculate my age in SQL Server with a birth date?
   如何计算带有生日SQL Server年龄？
4. How can I insert the current time by default in a SQL Server table?
   默认情况下，如何在SQL Server表中插入当前时间？
5. How can I check the total time that the employees of my company worked per day?
   如何查看公司员工每天工作的总时间？
6. How can I get the time of a specific region?
   如何获得特定地区的时间？
7. How can I get the time of a specified Standard time?
   如何获得指定标准时间的时间？

入门 (Getting started)

2. Which function should I use to get the current date in SQL Server? 我应该使用哪个函数来获取SQL Server中的当前日期？

   There are several methods:

   有几种方法：

    
   SELECT SYSDATETIME()  as [SYSDATETIME]
   ,SYSDATETIMEOFFSET()  as [SYSDATETIMEOFFSET]
   ,SYSUTCDATETIME() as  [SYSUTCDATETIME]
   ,CURRENT_TIMESTAMP  as [CURRENT_TIMESTAMP]
   ,GETDATE()   as [GETDATE]
   ,GETUTCDATE()  as [GETUTCDATE];  
    
   

   The results displayed are as follows:

   显示的结果如下：

   
   Figure 1. Different date time functions to show the date and time 图1.不同的日期时间功能显示日期和时间
   • SYSDATETIME shows the date and time of the SQL Server instance where it is running. The precision is 100 nanoseconds.
     SYSDATETIME显示运行SQL Server实例的日期和时间。 精度为100纳秒。
   • SYSDATETIMEOFFSET shows the time of the SQL Server instance where it is running zone offset of the UTC (Universal Time Coordinated). The precision is 100 nanoseconds
     SYSDATETIMEOFFSET显示SQL Server实例在其运行时的UTC区域偏移时间（协调世界时）的时间。 精度为100纳秒
   • SYSUTCDATETIME shows the time in UTC format of the SQL Server instance where it is running. The precision is 100 nanoseconds.
     SYSUTCDATETIME以UTC格式显示运行SQL Server实例的时间。 精度为100纳秒。
   • CURRENT_TIMESTAMP shows the current database time stamp of the SQL Server instance where it is running. The precision is 0.00333 seconds.
     CURRENT_TIMESTAMP显示正在运行SQL Server实例的当前数据库时间戳。 精度为0.00333秒。
   • It is similar to CURRENT_TIMESTAMP. Same precision.
     它类似于CURRENT_TIMESTAMP。 精度一样。
   • It is similar to SYSUTCDATETIME, but the precision is 0.000333 seconds.
     它类似于SYSUTCDATETIME，但精度为0.000333秒。
4. How can I get the current time in the format hh:mm:ss? 如何以hh：mm：ss格式获取当前时间？

   The FORMAT function was introduced in SQL Server 2012 and it is a very flexible way to convert your time to the format of your preference:

   SQL Server 2012中引入了FORMAT函数，它是一种将时间转换为首选项格式的非常灵活的方法：

    
   SELECT FORMAT(SYSDATETIME(),'hh:mm:ss')  as clockformat
    
   

   
   Figure 2. The time using the format function 图2.使用格式功能的时间

   How can I convert the date to the format MM/dd/yyyy?

   如何将日期转换为MM / dd / yyyy格式？

   You can convert using the FORMAT function (note that the Months requires the letter m uppercased to differentiate months from minutes)

   您可以使用FORMAT函数进行转换（请注意，月份要求大写字母m来区分月份和分钟）

    
   SELECT FORMAT(SYSDATETIME(),'MM/dd/yyyy')  as [dateformat]  	
     
   

   
   Figure 3. MM/dd/yyyy format 图3. MM / dd / yyyy格式

   Alternatively, you can use the convert function (this function was the most popular choice when FORMAT did not exist):

   或者，您可以使用convert函数（当FORMAT不存在时，此函数是最受欢迎的选择）：

    
   Select convert(nvarchar(20),SYSDATETIME(),101) [dateformat]
    
   

   101 is the value to get the format MM/dd/yyyy. For a complete list of formats, go to Convert to the date and time styles section.

   101是获取格式MM / dd / yyyy的值。 有关格式的完整列表，请转到“ 转换为日期和时间样式”部分。

6. How can I calculate my age in SQL Server if I have my birth date? 如果我有生日，该如何计算我在SQL Server中的年龄？

   DATEDIFF is a powerful function that can be used to find the difference in months, years, months, hours, minutes, seconds, etc. between two dates.

   DATEDIFF是一项功能强大的功能，可用于查找两个日期之间的月，年，月，小时，分钟，秒等之间的差异。

   The following example shows how to find the age of a person who was born on March 19 in 1979:

   以下示例显示了如何确定1979年3月19日出生的人的年龄：

    
   SELECT DATEDIFF(year, '1979-03-19', getdate()) as [years old];
    
   

   The result displayed is the following:

   显示的结果如下：

   
   Figure 4. Years old calculated using DATEDIFF 图4.使用DATEDIFF计算的岁数
8. How can I insert the current time by default in a SQL Server table?默认情况下，如何在SQL Server表中插入当前时间？

   We need to use a default constraint for this purpose. The default constraints allows having values by default in your tables. You can use functions as default values.

   为此，我们需要使用默认约束。 默认约束允许表中默认包含值。 您可以将函数用作默认值。

   The following example shows how to insert the current date using the getdate function as a default value of the registered time column:

   下面的示例显示如何使用getdate函数作为注册时间列的默认值插入当前日期：

    
   create table workingHours 
   (id int,name varchar(40),
   lastname varchar(40),
   [registered time] datetime default getdate())
    
   

   To insert a default value, use the word default. The following example shows how to insert a default value in the table created before:

   要插入默认值，请使用单词default。 以下示例显示如何在之前创建的表中插入默认值：

    
   insert into workingHours values(1,'John','Wayne',default)
    
   

   To verify the results, run the select statement:

   要验证结果，请运行select语句：

    
   select * from workingHours
    
   

   As you can see, the current time was inserted:

   如您所见，当前时间已插入：

   
   Figure 5. Default date value inserted 图5.插入的默认日期值
10. How can I check the total time that the employees of my company worked per day? 如何查看公司员工每天工作的总时间？

    We need a table with the checking time and checkout time. In this company, the employees work from 8 to 12 and 14 to 18:

    我们需要一个带有检查时间和结帐时间的表格。 在这家公司中，员工的工作时间是8到12和14到18：

     
    create table WorkedHours
    (id int,name varchar(40),
    lastname varchar(40),
    checkin datetime,
    checkout datetime)  	
     
    

    We will insert some data for testing purposes:

    我们将插入一些数据以进行测试：

     
    insert into WorkedHours values
    (1,'John','Wayne','2016-12-16 08:02:05','2016-12-16 12:03:45'),
    (2,'John','Wayne','2016-12-16 14:05:36','2016-12-16 18:01:33'),
    (3,'John','Wayne','2016-12-17 08:03:05','2016-12-17 12:07:45'),
    (4,'John','Wayne','2016-12-17 14:05:36','2016-12-17 18:11:33'),
    (5,'Peter','Jackson','2016-12-16 08:07:05','2016-12-16 12:03:45'),
    (6,'Peter','Jackson','2016-12-16 14:08:36','2016-12-16 18:01:33'),
    (7,'Peter','Jackson','2016-12-17 08:03:09','2016-12-17 12:06:33'),
    (8,'Peter','Jackson','2016-12-17 14:01:39','2016-12-17 18:12:36')
     
    

    Run a select to check the data:

    运行选择以检查数据：

     
    select * from WorkedHours
     
    

    If we do a select in the table, we will see that we have the data about two employees with the checking and checkout time in two different days:

    如果在表中进行选择，我们将看到我们拥有有关两名员工的数据，并在两天内分别有检查和结帐时间：

    
    Figure 6. Table values about employees and check-in and checkout dates 图6.有关员工以及入住和退房日期的表值

    The following queries will show the employees and the date where they worked less than 8 hours (480 minutes):

    以下查询将显示员工及其不到8小时（480分钟）的工作日期：

     
            with hoursworked
    as
    	(
     	SELECT name, lastname,DATEDIFF(minute, checkin, checkout) as 
    minutes,FORMAT(checkin,'yyyyMMdd') [date]
    		from WorkedHours
          )
     
    	select 
    	sum(minutes) [Total minutes per day],
    	name,
    	lastname,
    	[date]
    	from hoursworked
    	group by name,lastname,[date]  
     
    

    We used the function DATEDIFF to find the difference in minutes between the check-in and checkout dates:

    我们使用函数DATEDIFF查找入住和退房日期之间的分钟差：

     
    DATEDIFF(minute, checkin, checkout) as minutes,FORMAT(checkin,'yyyyMMdd')
     
    

    We also show the date in the format yyyyMMdd in order to group by date excluding hours, minutes and seconds:

    我们还以yyyyMMdd格式显示日期，以便按日期进行分组，但小时，分钟和秒除外：

     
      FORMAT(checkin,'yyyyMMdd')
     
    

    We SUM the total minutes:

    我们将总分钟数相加：

     
    sum(minutes) [Total minutes per day],
     
    

    Finally, we group the information by name, lastname and the date:

    最后，我们按名称，姓氏和日期对信息进行分组：

     
    group by name,lastname,[date]
    having sum(minutes) <480
     
    

    The query will show that John Wayne and Peter Jackson worked less than 8 hours (less than 480 minutes) on December 16:

    该查询将显示John Wayne和Peter Jackson在12月16日的工作时间少于8小时（少于480分钟）。

    
    Figure 7. Total minutes worked per day 图7.每天总工作时间
12. How can I get the time of a specific region? 如何获得特定地区的时间？

    You can use the SYSDATETIMEOFFSET function with the SWITCHOFFSET function. For example, to get the time in India, you need to add 5 hours 30 minutes to the SYSDATETIMEOFFSET:

    您可以将SYSDATETIMEOFFSET函数与SWITCHOFFSET函数一起使用。 例如，要获取印度时间，您需要向SYSDATETIMEOFFSET添加5小时30分钟：

     
    select  SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30') timeIndia
     
    

    The result of the query is the following:

    查询结果如下：

    
    Figure 8. Time in India 图8.印度时间

    If you do not like this format. You can always use the format function explained before:

    如果您不喜欢这种格式。 您始终可以使用前面说明的格式功能：

     
    select FORMAT(SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'), 'hh:mm:ss') timeIndia
     
    

    This query will show the time in India with the hh:mm:ss format:

    该查询将以hh：mm：ss格式显示印度时间：

    
    Figure 9. Time in India in format hh:mm:ss 图9.印度时间，格式为hh：mm：ss

    To verify the time zones available in SQL Server you can query the sys.time_zone_info view:

    要验证SQL Server中可用的时区，可以查询sys.time_zone_info视图：

     
    select * from sys.time_zone_info
     
    

    The query will show all the time zones available:

    该查询将显示所有可用的时区：

    
    Figure 10. Current time zones 图10.当前时区
14. How can I get the time of a specified Standard time? 如何获得指定标准时间的时间？

    We created a stored procedure for you, which will easily show you the time using a single keyword. For example, if I send the Pacific word to the stored procedure, I want to see the time of the Pacific regions. If I write UTC, the stored procedure will show the UTC times available:

    我们为您创建了一个存储过程，可以使用一个关键字轻松地为您显示时间。 例如，如果我将太平洋单词发送到存储过程，我想查看太平洋地区的时间。 如果我编写UTC，则存储过程将显示可用的UTC时间：

    We will use the following stored procedure:

    我们将使用以下存储过程：

     
    create procedure timezone
    @region varchar (100)
    as
    	  
    declare @utc varchar (8)
     
    select  name,FORMAT(SWITCHOFFSET(SYSDATETIMEOFFSET(), current_utc_offset), 'hh:mm:ss') timezone 
    from sys.time_zone_info
    where name like '%'+@region+'%'
     
    

    We specify the region and the stored procedure will calculate the time of the regions related using the format hh:mm:ss.

    我们指定区域，存储过程将使用hh：mm：ss格式计算相关区域的时间。

    For example to see the time in India, we can run the stored procedure as follows:

    例如，要查看印度的时间，我们可以运行存储过程，如下所示：

     
    execute timezone 'india'
     
    

    The result displayed is the following:

    显示的结果如下：

    
    Figure 11. Indian Standard Time 图11.印度标准时间

    The values are based on the system view of the figure 10. If we want to get the Pacific Standard Time, we can execute the stored procedure with the parameter set to Pacific:

    这些值基于图10的系统视图。如果要获取Pacific Standard Time，可以执行将参数设置为Pacific的存储过程：

     
    execute timezone 'Pacific'
     
    

    The stored procedure will show all the Standard times related to the word Pacific:

    该存储过程将显示与单词Pacific相关的所有标准时间：

    
    Figure 12. Different Pacific Standard times 图12.不同的太平洋标准时间

结论 (Conclusions)

We learned how to work with dates and time, how to detect the difference between two dates, how to set the current date as the default value, how to change the date and time format and how to get the time in a different time zone. If you have more questions related to time functions, do not hesitate to write your comments with your questions.

我们学习了如何使用日期和时间，如何检测两个日期之间的差异，如何将当前日期设置为默认值，如何更改日期和时间格式以及如何获取其他时区中的时间。 如果您还有其他与时间功能有关的问题，请不要犹豫，写下自己的评论。

参考资料 (References)

For more information, refer to these links:

有关更多信息，请参考以下链接：

• GETDATE (Transact-SQL) GETDATE（Transact-SQL）
• AT TIME ZONE (Transact-SQL) 时区（Transact-SQL）
• SWITCHOFFSET (Transact-SQL) 开关偏移量（Transact-SQL）

翻译自: https://www.sqlshack.com/faq-dates-sql-server/