convert into to hours minutes

Someone else setup the table and I cannot change it, so I am stuck with
what I have.
I have an INT field called hourminute.
The data is 3-4 digits, and is always based on hour/minute in 24-hour
format.
I need the output to be hh:mm. In the field, there is not a COLON and
that is what I need.
I have the following sql query that converts it correctly, but I need to
insert the :
select CONVERT(CHAR(5),hourminute,8)as thetime from booking
Help'
*** Sent via Developersdex http://www.examnotes.net ***SELECT CAST(hourminute / 100 AS char(2)) + ':' + RIGHT(CAST(hourminute AS
char(4)),2)
If you posted to this forum through TechNet, and you found my answers
helpful, please mark them as answers.
"Joey Martin" wrote:

> Someone else setup the table and I cannot change it, so I am stuck with
> what I have.
> I have an INT field called hourminute.
> The data is 3-4 digits, and is always based on hour/minute in 24-hour
> format.
> I need the output to be hh:mm. In the field, there is not a COLON and
> that is what I need.
> I have the following sql query that converts it correctly, but I need to
> insert the :
> select CONVERT(CHAR(5),hourminute,8)as thetime from booking
>
> Help'
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Works if there are 4 digits, but does not work if 3.
Example:
730 = 7 :0
725 = 7 :5
*** Sent via Developersdex http://www.examnotes.net ***|||Hi, Joey
You have used the CONVERT function using 8 for the style argument, but
you specified that the hourminute column is an int. The style argument
is only used if you convert a datetime, smalldatetime, float, real,
money, smallmoney or xml value. For any other data type (including
int), the style argument is ignored. See Books Online topic "CAST and
CONVERT (Transact-SQL)" for more informations.
You probably want to use something like this:
SELECT CONVERT(varchar(2),hourminute/100)+':'
+RIGHT(CONVERT(char(3),hourminute%100+10
0),2)
FROM booking
Razvan|||Hi, Mark
Your query works only for values greater than 1000.
Here is my sample data used for testing:
CREATE TABLE booking (hourminute int)
INSERT INTO booking VALUES (1234)
INSERT INTO booking VALUES (2307)
INSERT INTO booking VALUES (0)
INSERT INTO booking VALUES (105)
INSERT INTO booking VALUES (555)
Razvan|||"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1137522307.881961.56610@.g49g2000cwa.googlegroups.com...
> Hi, Mark
> Your query works only for values greater than 1000.
> Here is my sample data used for testing:
> CREATE TABLE booking (hourminute int)
> INSERT INTO booking VALUES (1234)
> INSERT INTO booking VALUES (2307)
> INSERT INTO booking VALUES (0)
> INSERT INTO booking VALUES (105)
> INSERT INTO booking VALUES (555)
> Razvan
This is ugly, but it works:
select left(right('000' + cast(hourminute as varchar(4)), 4), 2) + ':' +
right(right('000' + cast(hourminute as varchar(4)), 4), 2) from booking|||"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1137522307.881961.56610@.g49g2000cwa.googlegroups.com...
> Hi, Mark
> Your query works only for values greater than 1000.
> Here is my sample data used for testing:
> CREATE TABLE booking (hourminute int)
> INSERT INTO booking VALUES (1234)
> INSERT INTO booking VALUES (2307)
> INSERT INTO booking VALUES (0)
> INSERT INTO booking VALUES (105)
> INSERT INTO booking VALUES (555)
> Razvan
Another one:
select STUFF(REPLICATE('0', 4-LEN(hourminute)) + CAST(hourminute as
VARCHAR(4)), 3,0,':') FROM booking|||How about this one:
SELECT STUFF(RIGHT(CAST(hourminute+10000 as CHAR(5)),4),3,0,':')
FROM booking
Razvan|||Nothing fundamentally wrong with arriving at the desired format by using the
disivion; the problem was a result of using char as opposed to varchar.
CASTING the pieces into char was adding extra spaces onto the end, which was
throwing off the result of the RIGHT function.
SELECT CAST(hourminute / 100 AS varchar(2)) + ':' + RIGHT(CAST(hourminute AS
varchar(4)),2) .
This does produce some strange results as well. With ML's supplied sample
data, a hourminute value of 0 returns 0:0 . The below cleans that up:
SELECT
CONVERT(char(10),
CAST(
CAST(hourminute / 100 AS varchar(2)) + ':' + RIGHT(CAST(hourminute AS
varchar(4)),2)
AS datetime)
, 8)
FROM booking
"Joey Martin" wrote:

> Works if there are 4 digits, but does not work if 3.
> Example:
> 730 = 7 :0
> 725 = 7 :5
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1137525849.104051.25420@.g43g2000cwa.googlegroups.com...
> How about this one:
> SELECT STUFF(RIGHT(CAST(hourminute+10000 as CHAR(5)),4),3,0,':')
> FROM booking
> Razvan
Good STUFF Razvan. :-)