FAQ about Dates in SQL Server
FAQ about Dates in SQL Server
Author Recent Posts Daniel CalbimonteDaniel 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) SQL Partition overview - September 26, 2022 ODBC Drivers in SSIS - September 23, 2022 Getting started with Azure SQL Managed Instance - September 14, 2022
SQLShack
SQL Server training EspañolFAQ about Dates in SQL Server
December 22, 2016 by Daniel CalbimonteIntroduction
In this article, I compiled a list of FAQs and Answers about dates. Which function should I use to get the current date in SQL Server? How can I get the current time in the format hh:mm:ss? How can I calculate my age in SQL Server with a birth date? How can I insert the current time by default in a SQL Server table? How can I check the total time that the employees of my company worked per day? How can I get the time of a specific region? How can I get the time of a specified Standard time?Getting started
Which function should I use to get the current date in SQL Server? There are several methods: 12345678 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. 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: 123 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) 123 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): 123 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. 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: 123 SELECT DATEDIFF(year, '1979-03-19', getdate()) as [years old]; The result displayed is the following: Figure 4. Years old calculated using DATEDIFF 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: 123456 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: 123 insert into workingHours values(1,'John','Wayne',default) To verify the results, run the select statement: 123 select * from workingHours As you can see, the current time was inserted: Figure 5. Default date value inserted 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: 1234567 create table WorkedHours(id int,name varchar(40),lastname varchar(40),checkin datetime,checkout datetime) We will insert some data for testing purposes: 1234567891011 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: 123 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): 1234567891011121314151617 with hoursworkedas ( 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: 123 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: 123 FORMAT(checkin,'yyyyMMdd') We SUM the total minutes: 123 sum(minutes) [Total minutes per day], Finally, we group the information by name, lastname and the date: 1234 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 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: 123 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: 123 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: 123 select * from sys.time_zone_info The query will show all the time zones available: Figure 10. Current time zones 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: 1234567891011 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_infowhere 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: 123 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: 123 execute timezone 'Pacific' The stored procedure will show all the Standard times related to the word Pacific: Figure 12. Different Pacific Standard timesConclusions
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 CalbimonteDaniel 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) SQL Partition overview - September 26, 2022 ODBC Drivers in SSIS - September 23, 2022 Getting started with Azure SQL Managed Instance - September 14, 2022