Daniel Calbimonte

FAQ about Dates in SQL Server

December 22, 2016 by

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:

    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:

    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)

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

    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:

    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:

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

    To verify the results, run the select statement:

    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:

    We will insert some data for testing purposes:

    Run a select to check the data:

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

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

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

    We SUM the total minutes:

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

    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:

    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:

    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:

    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:

    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:

    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:

    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:


Daniel Calbimonte

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 is also a writer for SQL Server training material for certification exams.

View all posts by Daniel Calbimonte
Daniel Calbimonte
SQL Database development

About 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 is also a writer for SQL Server training material for certification exams. View all posts by Daniel Calbimonte

1,162 Views