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?
2. How can I get the current time in the format hh:mm:ss?
3. How can I calculate my age in SQL Server with a birth date?
4. How can I insert the current time by default in a SQL Server table?
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

1. Which function should I use to get the current date in SQL Server?

   There are several methods:

   1 2 3 4 5 6 7 8

   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
   • SYSDATETIME shows the date and time of the SQL Server instance where it is running. The precision is 100 nanoseconds.
   • 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
   • SYSUTCDATETIME shows the time in UTC format of the SQL Server instance where it is running. The precision is 100 nanoseconds.
   • CURRENT_TIMESTAMP shows the current database time stamp of the SQL Server instance where it is running. The precision is 0.00333 seconds.
   • It is similar to CURRENT_TIMESTAMP. Same precision.
   • It is similar to SYSUTCDATETIME, but the precision is 0.000333 seconds.
2. How can I get the current time in the format 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:

   1 2 3

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

   

   Figure 2. The time using the format function

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

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

   1 2 3

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

   

   Figure 3. MM/dd/yyyy format

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

   1 2 3

   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.

3. How can I calculate my age in SQL Server if I have my birth date?

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

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

   1 2 3

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

   The result displayed is the following:

   

   Figure 4. Years old calculated using DATEDIFF

4. How can I insert the current time by default in a SQL Server table?

   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:

   1 2 3 4 5 6

   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:

   1 2 3

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

   To verify the results, run the select statement:

   1 2 3

   select * from workingHours

   As you can see, the current time was inserted:

   

   Figure 5. Default date value inserted

5. 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:

   1 2 3 4 5 6 7

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

   We will insert some data for testing purposes:

   1 2 3 4 5 6 7 8 9 10 11

   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:

   1 2 3

   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

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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

   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:

   1 2 3

   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:

   1 2 3

   FORMAT(checkin,'yyyyMMdd')

   We SUM the total minutes:

   1 2 3

   sum(minutes) [Total minutes per day],

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

   1 2 3 4

   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:

   

   Figure 7. Total minutes worked per day
6. 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:

   1 2 3

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

   The result of the query is the following:

   

   Figure 8. Time in India

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

   1 2 3

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

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

   

   Figure 9. Time in India in format hh:mm:ss

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

   1 2 3

   select * from sys.time_zone_info

   The query will show all the time zones available:

   

   Figure 10. Current time zones

7. 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:

   We will use the following stored procedure:

   1 2 3 4 5 6 7 8 9 10 11

   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.

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

   1 2 3

   execute timezone 'india'

   The result displayed is the following:

   

   Figure 11. Indian Standard Time

   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:

   1 2 3

   execute timezone 'Pacific'

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

   

   Figure 12. Different Pacific Standard times

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)
• AT TIME ZONE (Transact-SQL)
• SWITCHOFFSET (Transact-SQL)

• Author
• Recent Posts

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

• PostgreSQL tutorial to create a user - November 12, 2023
• PostgreSQL Tutorial for beginners - April 6, 2023
• PSQL stored procedures overview and examples - February 14, 2023