Format function in SQL

[tcumming@smorgonsteel.com.au]

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.

TC

[Simon Hayes]

Re: Format function in SQL

See the various formats under CONVERT in Books Online:

select convert(char(6) , current_timesta mp, 112)

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

Simon

[Razvan Socol]

Re: Format function in SQL

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

SELECT RIGHT('0'+CONVE RT(varchar(2),Y ourNumber),2)

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

SELECT CONVERT(varchar (4),YEAR(GETDAT E()))
+RIGHT('0'+CONV ERT(varchar(2), MONTH(GETDATE() )),2)

or:

SELECT CONVERT(varchar (6),GETDATE(),1 12)

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

[tcumming@smorgonsteel.com.au]

Re: Format function in SQL

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

exactly what i needed

cheers,

TC

[Erland Sommarskog]

Re: Format function in SQL

(tcumming@smorg onsteel.com.au) writes:[color=blue]
> 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")[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp