Adding zero's to int

Question

Post reply

Adding zero's to int

mosmits

Newbie

Points: 5
More actions
April 5, 2010 at 3:55 pm

#142432

Hello and thanks ahead of time to all of your help.
I have a column that is an integer and it is hours and minutes here's example
1132
234
1
23
etc.
I want to add a zero to 234 to the left side and on 1 for instance I want to add a zero on the left and two on the right when it's ran so it will be 0234 so when i run it will be 02:34 and 01:00 respectively. Can someone point me in the right direction? Like do I need an if statement. I'm a little familar with Replicate but couldn't get it to work right.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281344 More actions · Answer 1

You will need to convert these to strings (varchar) in order to place a 0 in front of or behind an int.

To append or prepend the number with 0's, you will need to use a a conditional expression - case or if will work there.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

if 234 is 02:34 then why on Earth would 1 be anything but 00:01???? :blink:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

Jeff Moden (4/5/2010)
if 234 is 02:34 then why on Earth would 1 be anything but 00:01???? :blink:

Nice catch there Jeff.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Steve Jones - SSC Editor SSC Guru Points: 738743 More actions · Answer 4

Why are you doing this? Is it for reporting or are you considering storing the data in this format?

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 5

Steve Jones - Editor (4/5/2010)
Why are you doing this? Is it for reporting or are you considering storing the data in this format?

Heh... what the hey... Microsoft saw fit to do it (this mistake) in their job history logs and I'll just be that's where the data is being read from.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

mosmits (4/5/2010)
Hello and thanks ahead of time to all of your help.
I have a column that is an integer and it is hours and minutes here's example
1132
234
1
23
etc.
I want to add a zero to 234 to the left side and on 1 for instance I want to add a zero on the left and two on the right when it's ran so it will be 0234 so when i run it will be 02:34 and 01:00 respectively. Can someone point me in the right direction? Like do I need an if statement. I'm a little familar with Replicate but couldn't get it to work right.

Does the following code help you out?

create table #TestTable (

Col1 int

);

insert into #TestTable

select 1132 union all

select 234 union all

select 1 union all

select 23;

select

STUFF(RIGHT('00000000' + cast(Col1 as varchar(8)), 4),3,0,':')

from

#TestTable;

drop table #TestTable;

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 7

CirquedeSQLeil (4/5/2010)
Jeff Moden (4/5/2010)
if 234 is 02:34 then why on Earth would 1 be anything but 00:01???? :blink:
Nice catch there Jeff.

Heh... but still the OP has not replied on the matter. :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281344 More actions · Answer 8

Jeff Moden (4/5/2010)
CirquedeSQLeil (4/5/2010)
Jeff Moden (4/5/2010)
if 234 is 02:34 then why on Earth would 1 be anything but 00:01???? :blink:
Nice catch there Jeff.
Heh... but still the OP has not replied on the matter. :hehe:

Maybe tomorrow 🙂

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events