DATE

Extracting Time and Date in MS SQL Server: A Comprehensive Guide

intro

Microsoft SQL Server is a robust relational database management system (RDBMS) widely used for storing, managing, and analyzing structured data. This guide focuses on methods to extract the date and time components from a datetime data type in SQL Server, providing you with techniques to obtain either date-only or time-only values.

Tools used in the tutorial
Tool Description Link
SQL Server
SQL Server is a database management system by Microsoft

Understanding the datetime function in SQL Server

Before diving into the extraction process, it is important to understand what the datetime function in SQL Server is. In SQL Server, datetime is a data type that stores both date and time information. It can represent a single point in time with a precision of up to 3.33 milliseconds, ranging from January 1, 1753, to December 31, 9999. The datetime data type allows developers to work with temporal data, enabling them to store, retrieve, and manipulate date and time values in their databases.

Retrieving SQL date and time using SQL Server

To retrieve the current date and time in SQL Server, use the following query:

Copy
        
1 SELECT getdate();

This returns a datetime value, for example: 2023-03-01 11:50:05.627

Extracting date or time from datetime with CONVERT

What is the CONVERT function in SQL Server?

The CONVERT function in SQL Server allows you to convert an expression from one data type to another, making it a versatile tool for extracting the date or time portion from a datetime value.

Copy
        
1 CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Using CONVERT to extract the date from datetime

To extract the date from a datetime value, run this query:

Copy
        
1 SELECT CONVERT(date, getdate());

This returns the current date value with the starting time value. For instance, the result might be:

Sep 1 2023 12:00:00:AM

For SQL Server versions older than 2008, use this query instead:

Copy
        
1 SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()));

This returns the same result because it calculates the difference in days between the 'zero' date (1900-01-01) and the current date, then adds that difference back to the 'zero' date. This effectively removes the time portion, leaving only the date value.

Converting to a date in a particular format

To extract the date from a datetime value, run this query:

Copy
        
1 SELECT CONVERT(VARCHAR(10), getdate(), 111);

In this example, it returns 2023/03/01.

The style used here is 111, which is yyyy/mm/dd. There are various other styles to choose from. Some common types include:

StyleDisplay Format
101mm/dd/yyyy
102yyyy.mm.dd
103dd/mm/yyyy
104dd.mm.yyyy
105dd-mm-yyyy
110mm-dd-yyyy
111yyyy/mm/dd
106dd mon yyyy
107Mon dd, yyyy

When using VARCHAR, it's crucial to specify the appropriate length for the desired format. An incorrect length might lead to truncated or incorrectly formatted results. Unfortunately, there isn't a built-in way to automatically adjust the length based on the chosen style.

Using CONVERT to extract time

To extract the time portion from a datetime value, you can use the CONVERT function with the appropriate style:

Copy
        
1 SELECT CONVERT(VARCHAR, getdate(), 108);

This will return the time portion in the format hh:mm:ss. For example, if the datetime value is 2023-03-01 11:50:05.627, the result will be 11:50:05.

Here's a table with some common time formats that can be used with the CONVERT function in SQL Server:

StyleDisplay Format
108hh:mm:ss
114hh:mi:ss:mmm (24h)

Please note that the number of time formats available in SQL Server is more limited compared to date formats. However, you can still use custom formatting techniques to display time in the desired format.

For example, if you want to display the time in a 12-hour format with an AM/PM indicator, you can use the following query:

Copy
        
1 SELECT REPLACE(REPLACE(CONVERT(VARCHAR, getdate(), 109), 'AM', ' AM'), 'PM', ' PM')

This will return the time portion in the format hh:mm:ss AM/PM. If the datetime value is 2023-3-01 11:50:05.627, the result will be 11:50:05 AM.

The above query first converts the datetime value to a string using style 109, which includes the date and time in the format mon dd yyyy hh:mi:ss:mmmAM. Then, it replaces 'AM' and 'PM' with ' AM' and ' PM' respectively to add a space before the AM/PM indicator.

Extracting date from datetime with CAST

What is the CAST function in SQL Server?

CAST is a function in SQL Server that allows you to change the data type of an expression. It can be used to extract time from the datetime function or convert datetime values to date-only formats.

Copy
        
1 CAST ( expression AS data_type [ ( length ) ] )

Using CAST to extract the date from datetime

To extract the date from datetime, use the following query:

Copy
        
1 SELECT CAST(getdate() AS date);

Alternatively, cast it to any data type - we’re using varchar as an example:

Copy
        
1 SELECT CAST(getdate() AS varchar(10));

Using CAST to extract time

To extract the time portion from a datetime value using CAST, you can first cast the datetime value to a time data type

Copy
        
1 SELECT CAST(getdate() AS time);

This will return the time portion, for example 11:50:05.6270000.

FAQ

What is the difference between using CAST and CONVERT to extract date and time from a datetime?

  • CAST is a straightforward function that changes one data type to another (e.g., datetime to date).
  • CONVERT adds the ability to specify styles, which give you control over the format of the output string (e.g., yyyy-mm-dd, hh:mm:ss).If you only need a raw date or time data type, CAST is usually enough. If you require a specific textual format, use CONVERT with the appropriate style code.

How do I extract only the date portion from a datetime in older SQL Server versions (before 2008)?

Older SQL Server versions (prior to 2008) lack a separate date data type. Instead, you can use this workaround:

Copy
        
1 SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()));

This effectively strips off the time portion by:

  1. Calculating the day difference from the “zero date” (1900-01-01) to the current date.
  2. Adding that difference back to the “zero date,” resulting in a date-only value.

Which function should I use to extract just the time portion from a datetime?

You can use either CAST or CONVERT.

CAST approach, this returns a value like 11:50:05.6270000.

Copy
        
1 SELECT CAST(GETDATE() AS time);

CONVERT approach, this formats the time as hh:mm:ss.

Copy
        
1 SELECT CONVERT(VARCHAR(8), GETDATE(), 108);

How can I display a custom date format, such as yyyy/mm/dd or mm-dd-yyyy?

Use the style parameter with CONVERT.

Copy
        
1 SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [FormattedDate];

111 yields a format of yyyy/mm/dd. Different style codes produce various formats (e.g., 101 for mm/dd/yyyy, 105 for dd-mm-yyyy). Always ensure the VARCHAR length is sufficient to include the full output.

How do I get a 12-hour clock format with AM/PM?

You can use CONVERT with a style that includes 12-hour notation, then refine it if necessary.

Copy
        
1 SELECT REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 109), 'AM', ' AM'), 'PM', ' PM');

109 produces mon dd yyyy hh:mi:ss:mmmAM (or PM).

The REPLACE functions insert a space before AM/PM for clarity.

Why might I prefer storing only the date or only the time in SQL Server?

Storing only the date (using the date data type in SQL Server 2008 and above) ensures time components do not introduce confusion or inaccuracies when performing date-based comparisons or calculations.

Storing only the time (using the time data type) can simplify queries and calculations that focus purely on time-based comparisons, such as scheduling or logging events during the day.

Conclusion

In this guide, we've covered various techniques for extracting date and time components from datetime values in MS SQL Server using the CONVERT and CAST functions. These methods provide flexibility in handling datetime data, allowing for more precise formatting and extraction based on your specific needs. By mastering these techniques, you can enhance your ability to manage and manipulate date and time information effectively in your SQL Server databases.

Now that you're equipped with this knowledge, you can efficiently manage date and time information in your MS SQL Server projects.

Follow our blog for more news around the database space.

Dbvis download link img
About the author
TheTable
TheTable

The Table by DbVisualizer is where we gather together to learn about and simplify the complexity of working with database technologies.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

The Ultimate Guide to the SQL Server Date Format

author Antonello Zanini tags DATE SQL SERVER 9 min 2024-10-23
title

Adding Dates in SQL: A Complete Tutorial

author Antonello Zanini tags DATE DATETIME MySQL POSTGRESQL SQL SERVER 7 min 2024-04-15
title

SQL Add to Date Operations: A Complete Guide

author Antonello Zanini tags DATE DATETIME MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-04-01
title

Understanding the DATEDIFF SQL Function: A Comprehensive Guide

author TheTable tags DATE 7 min 2024-01-24
title

Date Formatting in Postgres: A Comprehensive Guide

author Leslie S. Gyamfi tags DATE POSTGRESQL 7 MINS 2023-12-11
title

PostgreSQL Truncate Table Reset Sequence Guide

author Antonello Zanini tags POSTGRESQL 6 min 2025-02-17
title

Clustered vs Non-Clustered Index: Complete SQL Guide

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2025-02-13
title

Queries and Subqueries in SQL: The Definitive Guide

author Lukas Vileikis tags DbVisualizer MySQL POSTGRESQL SQL SQL SERVER 7 min 2025-02-12
title

How to Optimize a Clustered Index Scan in SQL

author Antonello Zanini tags MySQL SQL SQL SERVER 7 min 2025-02-11
title

Expert Guide to Advanced SQL Joins

author Ochuko Onojakpor tags JOIN 6 min 2025-02-10

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.