Format function in SQL

Hi ... i'm sorry to hassle this user group but i have an urgent need
for some code - i have tried and tried to find a solutionn elsewhere -
but the problem is i don't really know what i am looking for.

What is the equivalent SLQ code for the below statement which works in
MS Access?

Format(7,"00")

Result: 07

or alternatively ...

what i am actually trying to do is return the string yyyymm based on
the current date. eg 200506 (June 2006)

Is there a simple way of doing this?

The code i am currently using is

CONVERT (char, DATEPART(yyyy, GETDATE())) + CONVERT (char, DATEPART(mm,
GETDATE()))

Result: 2005 !!!!

At least i thought i would get 20056. But obv what i am aiming for is
200506.

Thanks in advance.

TCSee the various formats under CONVERT in Books Online:

select convert(char(6), current_timestamp, 112)

But in general, it's better to format output in the front end, not in
the database.

Simon|||To format a number as text with a leading zero, you can use the
following trick:

SELECT RIGHT('0'+CONVERT(varchar(2),YourNumber),2)

To get the year and month of a date, formatted as "yyyymm" you can use:

SELECT CONVERT(varchar(4),YEAR(GETDATE()))
+RIGHT('0'+CONVERT(varchar(2),MONTH(GETDATE())),2)

or:

SELECT CONVERT(varchar(6),GETDATE(),112)

By the way, your code doesn't return '2005', as you think; it returns:
'2005 6 '
This is because the char data type has a fixed length (it doesn't trim
trailing blanks) and because the default size of a char is 30.

For more informations about datetime data types, see:
http://www.karaszi.com/SQLServer/info_datetime.asp

Razvan|||thanks heaps - the SELECT CONVERT(varchar(6),GETDATE(),1*12) worked a
treat!

exactly what i needed

cheers,

TC|||(tcumming@.smorgonsteel.com.au) writes:
> Hi ... i'm sorry to hassle this user group but i have an urgent need
> for some code - i have tried and tried to find a solutionn elsewhere -
> but the problem is i don't really know what i am looking for.
> What is the equivalent SLQ code for the below statement which works in
> MS Access?
> Format(7,"00")

While you already have gotten help with your urgent needs, permit me
to point out that Functions->String Functions in the T-SQL Reference
of Books Online is a good place to start. That and the CAST and
CONVERT topic.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp