DATES
MSSQL

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

intro

Microsoft SQL Server is a powerful relational database management system (RDBMS) that provides a platform for storing, managing, and analyzing structured data. In this guide, we'll focus on how to extract time from the datetime function and obtain date-only values using various methods in MS SQL Server.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
MICROSOFT SQL SERVER MICROSOFT SQL SERVER
THE MICROSOFT SQL SERVER DATABASE

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?

CONVERT is a function in SQL Server that allows you to convert an expression from one data type to another. It can be used to extract the date from a datetime value.

CONVERT syntax:

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

Remember to define the correct varchar length for each style. Specifying the length is essential because different date formats may have different character counts. If you don't provide the correct length, the output might be truncated or improperly formatted. Unfortunately, there isn't a built-in way to automatically adjust the length based on the chosen style.

DbVisualizer logo

If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.

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.

CAST syntax:

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 2 SELECT CAST(getdate() AS time);

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

Conclusion

In this guide, we have explored various methods for extracting time from datetime values and obtaining date-only values in MS SQL Server. By using the CONVERT and CAST functions, you can manipulate datetime values to suit your needs. We've looked at how to use CONVERT with various styles and lengths, as well as how to use CAST for extracting time from datetime. These techniques provide you with the flexibility to handle date and time values in various formats and situations, making your data more accessible and easier to work with. 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

SQL Formatter: Definition and Best Tools

author Antonello Zanini tags SQL 9 min 2024-05-20
title

SQL TRIM: Removing Extra Space Characters From a String

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-05-16
title

PostgreSQL Full Text Search: The Definitive Guide

author Antonello Zanini tags POSTGRESQL Search 11 min 2024-05-13
title

SQL UNION ALL: Keeping Duplicates When Combining Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL UNION 8 min 2024-05-09
title

SQL UNION Operator: How To Combine Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER UNION 9 min 2024-05-06
title

SQL CONVERT: The Handbook of Data Conversion in SQL

author Antonello Zanini tags CONVERT Data Conversion MySQL SQL SERVER 7 min 2024-05-02
title

SQL CASE Statement: Definitive Guide

author Antonello Zanini tags CASE ORACLE POSTGRESQL SQL SQL SERVER 5 min 2024-04-29
title

SQL REPLACE Function: A Comprehensive Guide

author TheTable tags SQL 5 min 2024-04-25
title

SQL ORDER BY Clause: Definitive Guide

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-04-22
title

SQL EXCEPT: Everything You Need to Know

author Leslie S. Gyamfi tags SQL 7 min 2024-04-18

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.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗