Format Time in SQL Server?

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)

Formatting is NOT done in the column. A time datatype is not stored as a string, internally it is stored as a decimal. Any formatting should be done at the application layer. But if you must do it in the database it is when the data is retrieved. — Sean Lange, Mar 23 '17 at 21:41
Possible duplicate of [How to cast the DateTime to Time](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time) — Zohar Peled, Mar 24 '17 at 07:21

SqlZim · Accepted Answer · 2017-03-24T11:55:48.930

8

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

edited Mar 24 '17 at 11:55

answered Mar 23 '17 at 22:02

SqlZim

37,248
6
41
59

1

Why `varchar(5)` when you already know it's always going to be the same length? Use `char(5)` instead. – Zohar Peled Mar 24 '17 at 06:38
1

You have to escape the ':' in your pattern like this: 'hh\:mm' – keuleJ Jun 26 '18 at 14:52
@keuleJ No escape necessary, here's a demo: http://rextester.com/WSWI99897 – SqlZim Jun 27 '18 at 18:55

score 0 · Answer 2 · answered Mar 24 '17 at 07:14

0

SELECT LEFT(CAST(getdate() AS Time),5) AS Hours

answered Mar 24 '17 at 07:14

score -2 · Answer 3 · answered Jul 03 '17 at 10:35

-2

SELECT FORMAT(SYSDATETIME(), 'h:mm tt')

answered Jul 03 '17 at 10:35

Himanshu Gupta

1
1

Format Time in SQL Server?

3 Answers3