CODEWITHSUNDEEP
sql-server
David Craven
David Craven

Reputation: 57

Rounding TimeDate value

I need help to round the StartTime and EndTime of a class to 15:00:00 instead of 15:00:00.0000000.

Below is a screenshot of the results from my query:

enter image description here

Below is my SELECT query:

SELECT DISTINCT Module.ModuleCode,ClassType.ClassTypeName,Convert(time,Class.StartTime) AS StartTime , Convert(time,Class.EndTime)AS EndTime, Building.BuildingName,RoomCode.RoomCode
FROM Class INNER JOIN Module ON Class.ModuleId = Module.ModuleId 

INNER JOIN RoomCode ON Class.RoomCodeId = RoomCode.RoomcodeId 
INNER JOIN Building ON RoomCode.BuildingId = Building.BuildingId 
INNER JOIN Days ON Class.DayId = Days.DayID 
INNER JOIN ClassType ON Class.ClassTypeId = ClassType.ClassTypeId WHERE Module.ModuleId =  6

enter image description here

Upvotes: 1

Views: 35

Answers (1)

SqlZim
SqlZim

Reputation: 38063

Well, for display in SSMS, you could convert to time(0) instead of time which is defaulting to time(7).

SELECT DISTINCT Module.ModuleCode,ClassType.ClassTypeName,Convert(time(0),Class.StartTime) AS StartTime , Convert(time(0),Class.EndTime)AS Endtime, Building.BuildingName,RoomCode.RoomCode
FROM Class INNER JOIN Module ON Class.ModuleId = Module.ModuleId 

INNER JOIN RoomCode ON Class.RoomCodeId = RoomCode.RoomcodeId 
INNER JOIN Building ON RoomCode.BuildingId = Building.BuildingId 
INNER JOIN Days ON Class.DayId = Days.DayID 
INNER JOIN ClassType ON Class.ClassTypeId = ClassType.ClassTypeId WHERE Module.ModuleId =  6

Upvotes: 3

Related Questions