Problem with Datepart

[Vimalsoft Pty Ltd]

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

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[J4amieC]

Why in the world would you be formatting your time in SQL? SQL Is for extracting data (and it seems your decord is already of type DateTime) and a client application should be responsible for formatting that data. As an aside, using spaces in fields or field alias' is generally bad form.

[Not Active]

Convert with style 108 will return the format as hh:mm:ss. If you don't want seconds, just strip it off.

---

I know the language. I've read a book. - _Madmatt

[David Skelly]

Damn, pipped to the post again! Note to self: must type faster.

[David Skelly]

The reason you are seeing this is because the minute part of 14:00 is 0. So, you end up getting 14 for the hour part and 0 for the minute part. Hence a result of 14:0. I agree with the previous poster, SQL is not the best place to reformat time, but sometimes the requirement is there and you have to do it. You should look into the format options of CONVERT. I think one option for you would be to convert starttime and endtime to strings using format 108 and then trim off the seconds if you don't want them. http://msdn.microsoft.com/en-us/library/ms187928.aspx[^] I'm also not convinced by your WHERE clause at the end. That looks to me like a potential performance killer, converting dates to do a selection like that. I may be wrong but I doubt if it will use an index for that so you will end up scanning over every row in the table.

[Not Active]

Maybe you need to upgrade the 14.4 modem to broadband ;P

---

I know the language. I've read a book. - _Madmatt

[Vimalsoft Pty Ltd]

Good Day in have Resolved it by adding

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

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/