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
