T-SQL Convert Function and Display Time in Hour:Minute Format from a Datetime Data Type Column or Variable
SQL developers and SQL Database administrators may require to get time in HOUR:MINUTE format from a datetime column or variable in order to return in a select query and display time information to the user.
The easiest way in t-sql to manipulate datetime data type columns and variables is using the built-in CONVERT datetime convertion function.
Using the Convert function t-sql developers can convert part of a datetime data into a varchar() or nvarchar() data type with specific length given as parameter to the Convert datetime function.
Convert function syntax :
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
The most remarkable point in the conversion Convert function is the magical style parameter used in the convert syntax.
Using style with different values you can get the output converted data in different formats like 'dd/mm/yy', 'mm/dd/yy', 'hh:mm:ss', 'yymmdd' or 'dd mon yyyy hh:mi:ss:mmmAM' and like many others.
To fetch time data out from a datetime data type column or datetime variable the best method or the most suitable style parameter is using the style 108.
The style 108 will return time in 'hh:mm:ss' format which means time in hour-minute-second format.
Here are some t-sql code samples illustrating the usage of Convert function with style 108
SELECT
CONVERT(VARCHAR(8) , GETDATE() , 108) AS HourMinute,
CONVERT(VARCHAR(5) , GETDATE() , 108) AS HourMinuteSecond
And the output of the example sql select queries is as follows :
Here is the source code of a t-sql user-defined function that you can use during your t-sql programming which returns time part of the datetime input variable in hh:mi format.
You can adopt this for returning hh:mi:ss format.
CREATE FUNCTION GetTimeAsChar
(
@Time datetime
) returns CHAR(5)
BEGIN
DECLARE @val AS CHAR(5)
SELECT @val = CONVERT(VARCHAR(5), @Time, 108)
RETURN @val
END
GO
SELECT dbo.GetTimeAsChar(GETDATE()) HourMinute