Month name

how do i return the month in text from a number?
the vb function is monthname(), there does not seem to be
an equivelent in TSQL
Use the DATENAME() function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL
|||Hi,
select datename(month,getdate())
Thanks
Hari
MCDBA
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL
|||what i am trying to do is convert a number eg 5, into its
month value eg 'september' all date functions such as
datename() & month() seem to ask for a date as there input
parameter

>--Original Message--
>Use the DATENAME() function.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...[vbcol=seagreen]
be
>
>.
>
|||The equivalent of VB MonthName() would be something like this:
SELECT DATENAME(MONTH,DATEADD(MONTH,@.month,-1))
where @.month is the month number.
More generally, DATENAME is used to return the month name from a DATETIME or
SMALLDATETIME value.
David Portas
SQL Server MVP
|||Declare @.Month as char(2)
Declare @.Date as char(10)
set @.Month = '06'
set @.Date = '01/' + @.Month + '/1900'
SELECT DATENAME(month, @.Date) AS 'Month Name'
You may need to modify the @.Date string depending where in
the world you are.
J

>--Original Message--
>what i am trying to do is convert a number eg 5, into its
>month value eg 'september' all date functions such as
>datename() & month() seem to ask for a date as there
input
>parameter
>message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>be
>.
>
|||> You may need to modify the @.Date string depending where in
> the world you are.
Not if you use a standard, non-ambiguous format!
SET @.Date = '1900' + @.Month + '01'
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Hi,
you can use the DATENAME(), but then you have to 'create' a date first:
DECLARE @.MonthNumber integer;
SET @.MonthNumber=11;
select datename(month,
CAST('1/'+CASE WHEN @.MonthNumber <10 THEN CAST(@.MonthNumber AS char(1))
ELSE CAST(@.MonthNumber AS char(2)) END +'/2004' AS DATETIME));
Because the month number has to be casted to a char first, distinguish
the two cases : month-number is one or two digit(s). (Perhaps a trim
function could be used instead of CASE WHEN )
Then cast the date string to a date and use the datename function. I
used the dateformat dd/mm/yyyy.
mat schrieb:[vbcol=seagreen]
> what i am trying to do is convert a number eg 5, into its
> month value eg 'september' all date functions such as
> datename() & month() seem to ask for a date as there input
> parameter
>
> message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>
> be

Month name

how do i return the month in text from a number?
the vb function is monthname(), there does not seem to be
an equivelent in TSQLUse the DATENAME() function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message news:cfdb01c4399a$26df1f00$a101
280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL|||Hi,
select datename(month,getdate())
Thanks
Hari
MCDBA
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL|||what i am trying to do is convert a number eg 5, into its
month value eg 'september' all date functions such as
datename() & month() seem to ask for a date as there input
parameter

>--Original Message--
>Use the DATENAME() function.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
be[vbcol=seagreen]
>
>.
>|||The equivalent of VB MonthName() would be something like this:
SELECT DATENAME(MONTH,DATEADD(MONTH,@.month,-1))
where @.month is the month number.
More generally, DATENAME is used to return the month name from a DATETIME or
SMALLDATETIME value.
David Portas
SQL Server MVP
--|||Declare @.Month as char(2)
Declare @.Date as char(10)
set @.Month = '06'
set @.Date = '01/' + @.Month + '/1900'
SELECT DATENAME(month, @.Date) AS 'Month Name'
You may need to modify the @.Date string depending where in
the world you are.
J

>--Original Message--
>what i am trying to do is convert a number eg 5, into its
>month value eg 'september' all date functions such as
>datename() & month() seem to ask for a date as there
input
>parameter
>
>message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>be
>.
>|||> You may need to modify the @.Date string depending where in
> the world you are.
Not if you use a standard, non-ambiguous format!
SET @.Date = '1900' + @.Month + '01'
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hi,
you can use the DATENAME(), but then you have to 'create' a date first:
DECLARE @.MonthNumber integer;
SET @.MonthNumber=11;
select datename(month,
CAST('1/'+CASE WHEN @.MonthNumber <10 THEN CAST(@.MonthNumber AS char(1))
ELSE CAST(@.MonthNumber AS char(2)) END +'/2004' AS DATETIME));
Because the month number has to be casted to a char first, distinguish
the two cases : month-number is one or two digit(s). (Perhaps a trim
function could be used instead of CASE WHEN )
Then cast the date string to a date and use the datename function. I
used the dateformat dd/mm/yyyy.
mat schrieb:[vbcol=seagreen]
> what i am trying to do is convert a number eg 5, into its
> month value eg 'september' all date functions such as
> datename() & month() seem to ask for a date as there input
> parameter
>
> message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>
> be
>

Month name

how do i return the month in text from a number?
the vb function is monthname(), there does not seem to be
an equivelent in TSQLUse the DATENAME() function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL|||Hi,
select datename(month,getdate())
Thanks
Hari
MCDBA
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL|||what i am trying to do is convert a number eg 5, into its
month value eg 'september' all date functions such as
datename() & month() seem to ask for a date as there input
parameter
>--Original Message--
>Use the DATENAME() function.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>> how do i return the month in text from a number?
>> the vb function is monthname(), there does not seem to
be
>> an equivelent in TSQL
>
>.
>|||The equivalent of VB MonthName() would be something like this:
SELECT DATENAME(MONTH,DATEADD(MONTH,@.month,-1))
where @.month is the month number.
More generally, DATENAME is used to return the month name from a DATETIME or
SMALLDATETIME value.
--
David Portas
SQL Server MVP
--|||Declare @.Month as char(2)
Declare @.Date as char(10)
set @.Month = '06'
set @.Date = '01/' + @.Month + '/1900'
SELECT DATENAME(month, @.Date) AS 'Month Name'
You may need to modify the @.Date string depending where in
the world you are.
J
>--Original Message--
>what i am trying to do is convert a number eg 5, into its
>month value eg 'september' all date functions such as
>datename() & month() seem to ask for a date as there
input
>parameter
>>--Original Message--
>>Use the DATENAME() function.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>
>>"mat" <anonymous@.discussions.microsoft.com> wrote in
>message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>> how do i return the month in text from a number?
>> the vb function is monthname(), there does not seem to
>be
>> an equivelent in TSQL
>>
>>.
>.
>|||> You may need to modify the @.Date string depending where in
> the world you are.
Not if you use a standard, non-ambiguous format!
SET @.Date = '1900' + @.Month + '01'
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hi,
you can use the DATENAME(), but then you have to 'create' a date first:
DECLARE @.MonthNumber integer;
SET @.MonthNumber=11;
select datename(month,
CAST('1/'+CASE WHEN @.MonthNumber <10 THEN CAST(@.MonthNumber AS char(1))
ELSE CAST(@.MonthNumber AS char(2)) END +'/2004' AS DATETIME));
Because the month number has to be casted to a char first, distinguish
the two cases : month-number is one or two digit(s). (Perhaps a trim
function could be used instead of CASE WHEN )
Then cast the date string to a date and use the datename function. I
used the dateformat dd/mm/yyyy.
mat schrieb:
> what i am trying to do is convert a number eg 5, into its
> month value eg 'september' all date functions such as
> datename() & month() seem to ask for a date as there input
> parameter
>
>>--Original Message--
>>Use the DATENAME() function.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>
>>"mat" <anonymous@.discussions.microsoft.com> wrote in
> message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>>how do i return the month in text from a number?
>>the vb function is monthname(), there does not seem to
> be
>>an equivelent in TSQL
>>
>>.

Month Function - left pad single digit months

Month(now()) returns 1 in January. How do I left pad that 1 such that
it will return 01 for all single digit months?
Thanks.If you need an expression in report design, try this:
=IIF(Month(Now())<10,"0" & Month(Now()),"" & Month(Now()))
Or, even simple:
=Right("0" & Month(Now()),2)
"Scott" <SHBOSTON@.gmail.com> wrote in message
news:77e80f94-f3bf-49fc-a8ec-d4c526066713@.v17g2000hsa.googlegroups.com...
> Month(now()) returns 1 in January. How do I left pad that 1 such that
> it will return 01 for all single digit months?
> Thanks.

month and 2 digit year

How can I get the month and 2 digit year from a date? For example, how could
I return the month/year like my DESIRED RESULTS 1 & 2 below? Both examples
equal January, 2005? DATEPART seems to only allow 4 digit years.
CODE:
SELECT DATEPART(yy, GETDATE())
Desired Results 1: 05-1
Desired Results 2: 1-051.
SELECT REPLACE(LEFT(CONVERT(char(8), GETDATE(), 2),5),'.','-')
2.
SELECT RIGHT(CONVERT(char(8), GETDATE(), 5),5)
"Scott" wrote:

> How can I get the month and 2 digit year from a date? For example, how cou
ld
> I return the month/year like my DESIRED RESULTS 1 & 2 below? Both examples
> equal January, 2005? DATEPART seems to only allow 4 digit years.
>
> CODE:
> SELECT DATEPART(yy, GETDATE())
> Desired Results 1: 05-1
> Desired Results 2: 1-05
>
>

Money conversion

Hi!

When I write:

'SELECT Amount FROM tTransaktion'

I get returnvalues such as '12000.0000'. Instead, I want it to return '12 000'.

The Amount column is of datatype money. Is this possible!?

Thanks!

select amount, amt, left(amt, len(amt) - 3) as [What you want]
from
(
select amount, replace(convert(varchar(20), amount, 1), ',', ' ') as amt
from
(
select convert(money, 12000) as amount
) a
) b|||

Sure, it's possible using the STR function.

declare @.value money

set @.value = 12000.00

select str(@.value,8,0)

The question is why? It is best to let the user interface handle the display of values and just send back raw values. Also, the money datatype has some issues with roundoff that is covered here: http://www.aspfaq.com/show.asp?id=2503

|||Louis - you're absolutely right -> stupid of me not to let the user interface handle it. Thanks for your answers!|||

Stupid, nah! I still have to fight myself to not format data in SQL since I have control over it and I am addicted to SQL :)

The UI programmers like it too since it is easier on them, but they are also the ones that argue about scalability of the database server, so offloading CPU work like this to them is easier to justify. Plus, you can use the regional settings of the client to display the data as they desire (which is a good thing too.)

|||

If you need commas you can use this

declare @.value money

set @.value = 12000.00

select convert(varchar,@.value,1)

Denis the SQL Menace

http://sqlservercode.blogspot.com/