Basic Logics · Datetime functions · Real Scenario Logic's

To Fetch Formatted Time from Date time in SQL Server

Introduction

In this blog post, we will see How to format the time part from Date time in SQL Server. Here, we have mentioned some of the common and easy methods to achieve this.

To Fetch Formatted Time from Date time

Method 1 – CONVERT

SQL Script

SELECT 
GETDATE() AS INPUT,
CONVERT(VARCHAR(5),GETDATE(),108) AS EXPECTED_OUTPUT;

Image Pic

Formatted Time from Date time

The above Code Uses GETDATE() in the Script. If we need to use Azure Datetime that needs to be common in both On-premises and Azure, then we can use like below.

SQL Script – Common for On-premises and Azure

DECLARE @Current_DateTime DATETIME= SYSDATETIMEOFFSET() AT TIME ZONE 'India Standard Time';

SELECT 
@Current_DateTime AS INPUT,
CONVERT(VARCHAR(5),@Current_DateTime,108) AS EXPECTED_OUTPUT;

Image Pic

Formatted Time from Azure Date time

NOTE –

We can also Use Any of the following methods and formats to get the same expected output. So, we can also use 8, 14, 24, 114 instead of 108 in the above scripts.

Time Formats in Date and Time Conversions

Method 2 – FORMAT

SQL Script – Common for On-premises and Azure

DECLARE @Current_DateTime DATETIME= SYSDATETIMEOFFSET() AT TIME ZONE 'India Standard Time';
SELECT 
	@Current_DateTime AS INPUT,
	FORMAT(@current_datetime,'HH:mm') AS EXPECTED_OUTPUT;

Image Pic

NOTE –

Here, while using the Format Function, we need to know the difference between upper and lower case differences. ‘HH’ and ‘hh’ are both same(represents hours) but ‘mm’ and ‘MM’ are different. ‘MM‘ gives us Month like ’02’ and ‘mm‘ gives us Minutes like ’55’ in this case. please make sure, we don’t try ‘Mm’ or ‘mM’ since all these would give us different values.

Conclusion

Thus, we saw How to format the time part from Date time to match with our expected output in SQL Server that is common for both On-premises and Azure SQL DB. Unit Tested as well.

Thanks for reading and Follow Blog and Show your Support for many more interesting upcoming Posts!

Advertisement

3 thoughts on “To Fetch Formatted Time from Date time in SQL Server

  1. Another method is to make use of FORMAT function

    DECLARE @Current_DateTime DATETIME= SYSDATETIMEOFFSET() AT TIME ZONE ‘India Standard Time’;

    SELECT
    @Current_DateTime AS INPUT,format(@current_datetime,’HH:mm’) as EXPECTED_OUTPUT;

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s