Problem with Datepart

[vuyiswamb]

Good Day all

i have the Following Query

DECLARE @CurrentTime DATETIME
SET @CurrentTime = CURRENT_TIMESTAMP
select tr.Descr [Room], tb.Purpose [Purpose], tb.Description [Description],
convert(varchar,datepart(hour,tb.starttime))+':'+convert(varchar,datepart(minute,tb.starttime)) [Start Time],
convert(varchar,datepart(hour,tb.endtime))+':'+convert(varchar,datepart(minute,tb.endtime)) [End Time],
tu.name [Requested by] from tbl_booking tb inner join tbl_resource tr
on tb.resources = tr.id
inner join tbl_user tu on tu.id = tb.RequestedByUser
where (day(startdate) = day(@CurrentTime))and(month(startdate)=month(@CurrentTime))and(year(startdate)=year(@CurrentTime))and(tb.status=1)
order by [Room],[Start Time]

and in the [Start Time]and [End Time] it gives me time that is not Complete

it Gives this

14:0

instead of

14:00

Thanks

[abhijit]

You could pad the output with extra values.

[abhijit]

Here's an example with GetDate()

--Tested On Microsoft SQL Server  2000 - 8.00.2040 
select Left('0'+ convert(varchar,datepart(hour,getdate())),2)

[brucevde]

There is no need to use the DatePart function.

The Convert function can format the datetime fields into hh:mm.

select tr.Descr [Room], tb.Purpose [Purpose], tb.Description [Description],
convert(varchar(5), tb.starttime, 108) [Start Time],
convert(varchar(5), tb.endtime, 108) [End Time],
tu.name [Requested by] 
from tbl_booking tb inner join tbl_resource tr