hh:mm in sql server (1 Viewer)

[roosn]

hi
i have a date field in sql server 2000, smalldatetime data type
what i cant get to is the hh:mm

for example

31/08/2005 12:01:00

using

CAST(DATEPART(hh, dbo.tbl_log.date) AS varchar) + ':' + CAST(DATEPART(mi, dbo.tbl_log.date) AS varchar)

i get 12:1

what i require is 12:01

thank you in advance

 

[SQL_Hell]

Hi,

To deal with the lack of leading zeros, use a case statement...like so:

select 
case
when len(CAST(DATEPART(hh, getdate()) AS varchar)) =1 then '0' + CAST(DATEPART(hh, getdate()) AS varchar) 
else CAST(DATEPART(hh, getdate()) AS varchar)
end
+ ':' + 
case
when len(CAST(DATEPART(mi, getdate()) AS varchar)) =1 then '0' + CAST(DATEPART(mi, getdate()) AS varchar) 
else CAST(DATEPART(mi, getdate()) AS varchar)
end

 

[roosn]

tried and tested
thank you