Problem with Datepart

Posted by Vuyiswamb under Sql Server on 11/12/2009 | Views : 1987 | Status : [Member] [MVP] [Administrator] | Replies : 2
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

Thank you for posting at Dotnetfunda
[Administrator]



Responses

Posted by: Dbdon on: 11/12/2009 [Member] Starter

Up
0
Down
hi Vuyiswamb,

i dont see any problem in your query, i took the heart of your query modified it and executed, it went well with me (i used sql 2005)

"select
convert(varchar,datepart(hour,'2009-11-12 10:40:10.270'))+':'+convert(varchar,datepart(minute,'2009-11-12 10:40:10.270')) [Start Time],
convert(varchar,datepart(hour,'2009-11-12 09:40:10.270'))+':'+convert(varchar,datepart(minute,'2009-11-12 09:40:10.270')) [End Time]"

it gives me the right answer as "10:40" and "09:40"

on a different note jsut check if you execute
select 'asdfg' and get it as it is as a result, if you see that you are just getting asdf, i believe you got the problem's root.

Cheers :)

Vuyiswamb, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 11/13/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Thanks for your Reply

i have resolved it by adding it this way

right('0'+convert(varchar(2),datepart(hour,tb.starttime)),2) + ':' + right('0'+convert(varchar(2),datepart(minute,tb.starttime)),2) AS[Start Time],
right('0'+convert(varchar(2),datepart(hour,tb.endtime)),2) + ':' + right('0'+convert(varchar(2),datepart(minute,tb.endtime)),2) AS [End Time],


Thanks

Thank you for posting at Dotnetfunda
[Administrator]

Vuyiswamb, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response