ensuring that an sql int returns 2 digits by default

[mike55]

I have two columns in my database, one called hours, the other called minutes, both of which are of datatype int. When my users submit a time on a web page, the value is broken up and placed in the appropriate column.

I am using a datagrid to display all the times, originally I was displaying the hours and minutes in two different columns. However, I now need to display them in a single column, and I want to seperate the hours and minutes by using the symbol ":".

I am using:

CONVERT(varchar, Hour, 110) + : + CONVERT(varchar, Minute, 110) AS times

The code works properly, the only problem that I am having is if the minute value is less than 10, then I need to append a zero to the start. I would also like to do the same for the hours if they are less than 10.

I know I could loop through the data using my .net code, but I could have thousands of records and that does not appeal to performance. Is there any way I can either set the minute and hours to have a 2 digit with both of them defaulting to "0".

Mike55.

 

[PlausiblyDamp]

You might try using the STR function in SQl to format the integer - not tried this personally but it might help.

Failing that it might be easier in the long run to handle the formatting in the UI rather then the DB anyway as databases arent really the most suitable tool for this kind of thing.

 

[Harkon]

greetings

try this:

select
cast(myHours as varchar(5)) +
case when len(myMinutes) < 2 then 0 + cast(myMinutes as varchar(5)) else cast(myMinutes as varchar(5)) end
FROM myTable