convert of int to char with a rule below

[Lesio]

How do I convert integer number from 1 - 9 to a char '01' - '09' and in the same time integer 10 + to same char with two characters ?

 

[vongrunt]

select right('00' + convert(varchar(2), <insert number here>), 2)

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Burglar]

The following will convert any one or 2 digit number to 2 characters.

select right('0' + convert(varchar(2), 1), 2)

 

[Burglar]

Ooops...I'm too slow I guess

 

[jbenson001]

Just another way, but I think vongrunt's is cleaner:

declare @i int
set @i = 2


select replicate ('0', 2 - Len(@i)) + Convert(varchar(2), @i)

 

[Lesio]

Thanks to all of you !

Can anybody help me a step further now. I have a date record with date_ts, year_id, month_id, quarter_id, week_id. Month_id, quarter_id and week_id are unique keys that start with value 1 from Jan 1st 1900. Year_Id is 1900, 1901 .. 2006. Any way to create
- week of year (values 1-53)
- week of month (values 1-5)
- week of quarter (values 1-13)
through a regular sql command ?

 

[SQLDenis]

Don't know if this is correct but you can modify it
change getdate with your date

select datepart(wk,getdate()) --week
select datepart(wk,getdate())/datepart(wk,getdate()) --week of quarter
select datepart(wk,getdate())/datepart(m,getdate()) --week of month



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

typo

should be

select datepart(wk,getdate()) --week
select datepart(wk,getdate())/datepart(qq,getdate()) --week of quarter
select datepart(wk,getdate())/datepart(m,getdate()) --week of month



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Ok the solution that I posted works for today and will work fro the week. However it is wrong for a date of 6/1/2005
I will get back to you with a solution



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Fun stuff!!!
declare @date datetime
select @date = '8/29/2005' -- getdate()
select datepart(wk,@date) --week
select datepart(wk,@date)-(datepart(qq,@date)-1)* 13 --week of quarter
select ceiling((datepart(d,@date))/7.0) --week of month

looks right but no warranty



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[AFK1]

How about if I want to convert a Char into Int. I have a char value '012345' that I need to insert into int Filed. I need to insert whole value without getting rid of 0.How do I do that then??