How to calculate two dates in SQL-CodePudding

I have an MS sql database with several tables in it,

I use JOIN to connect 3 tables using query:

    SELECT 
  c.Date   ,c.CalendarDayId   ,c.VehicleId   ,c.CompanyId   ,c.DepartmentId    ,c.IsDeleted   
  ,cds.CalendarShiftId
  ,cs.AvailableTime
FROM 
  [TEST].[dbo].[Calendar] c
  JOIN  [TEST].[dbo].[CalendarDayShift] cds ON cds.CalendarDayId = c.CalendarDayId
  jOIN [TEST].[dbo].[CalendarShift] cs ON cs.Id = cds.CalendarShiftId
WHERE 
  c.VehicleId   IN('2') 
  AND c.Date BETWEEN '2021-07-01 00:00:00.000' AND '2021-07-01 23:00:00.000'  
  AND c.IsDeleted =0

and I get : 2022-01-11 07:45:00.000// 2022-01-11 07:45:00.000

Column [AvailableTime] is datatime How can I Calculate this date from column [AvailableTime] to get result like:

15:30

CodePudding user response：

Do you mean how you can format the date to just get a time ?

For doing that see the following link: https://www.w3schools.com/sql/func_sqlserver_convert.asp

If you want to know which datatype you have to use to get just a time then use the datatype "time".

CodePudding user response：

Use belowed function at your query instead of AvailableTime.

CONVERT(VARCHAR(5),AvailableTime, 108)-- Gets only HH:mm

Using varchar(5) will automatically truncate the date to remove the seconds.

Or you can also use these function too:

SELECT LEFT(CONVERT(VARCHAR,@date,108),5)

For more information about date formatting and format styles you can check these.