Format Time in SQL?

Question

How can I format time to only display Hours and minutes? For example Id like LessonTime TIME(0), to show 12:00 rather than 12:00:00.

USE [Assignment]
GO
PRINT 'Creating database tables...'
CREATE TABLE [PupilDetails](
Pupil_ID INT  NOT NULL, 
FName VARCHAR(20),
LName VARCHAR(20),
DOB DATE,
LessonDay VARCHAR (10),
LessonTime TIME(0),
GuardianFname VARCHAR(20),
GuardianLname VARCHAR(20),
ContactNum VARCHAR(15),
AddressLine1 VARCHAR(20),
AddressLine2 VARCHAR(20),
Teacher_ID INT NOT NULL,
PRIMARY KEY (Pupil_ID),
CONSTRAINT FK_PupilDetails FOREIGN KEY (Teacher_ID)
REFERENCES Teachers(ID)

Solution

The value of time or datetime data type is not stored with format in sql server. If you want to see the time in a different format you can manipulate the way that time and datetime data types are displayed when converted to a varchar (or nvarchar,nchar,char) data type using some built in functions.

Most often with convert() styles

select convert(char(5),convert(time(0),sysdatetime()))

returns: 22:01

In sql server 2012+ you can use format()

select format(sysutcdatetime(),'HH:mm')

returns: 22:01

But format() can be slower, take a look here: format() is nice and all, but… - Aaron Bertand