Being new to SQL server T-SQL (working with DB2 / ORACLE) is there a function like monthname() in SQL server. I.e. a function that takes date as input and returns the Monthname (like AUG or AUGUST)??This example extracts the month name from the date returned by GETDATE.
SELECT DATENAME(month, getdate()) AS 'Month Name'
Here is the result set:
Month Name
----------
February|||Sorry, where can I put the date where I can extract the monthname from?
I want to add an object that displays 'JAN' or Januari if I have a date like:
01/01/2003|||SELECT DATENAME(month, '01/01/2003') AS 'Month Name'|||Thank you very much..............
Showing posts with label monthname. Show all posts
Showing posts with label monthname. Show all posts
Friday, March 23, 2012
MonthName function in IIF
Hi,
We are getting a runtime #error when using MonthName in an IIF function more
than once as follows:
IIF(Parameters!ReportPeriod.Value <
7,MonthName(Parameters!ReportPeriod.Value +
6),MonthName(Parameters!ReportPeriod.Value))
Parameters!ReportPeriod is an integer, and the expression works Ok until the
second MonthName is added.
Thanks,
MattDoes this work (by adding an explicit cast)?
= IIF(Parameters!ReportPeriod.Value > 7,
MonthName(Parameters!ReportPeriod.Value + 6),
MonthName(CInt(Parameters!ReportPeriod.Value)))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:E9371099-FDAB-4A79-95D5-8344C8595347@.microsoft.com...
> Hi,
> We are getting a runtime #error when using MonthName in an IIF function
> more
> than once as follows:
> IIF(Parameters!ReportPeriod.Value <
> 7,MonthName(Parameters!ReportPeriod.Value +
> 6),MonthName(Parameters!ReportPeriod.Value))
> Parameters!ReportPeriod is an integer, and the expression works Ok until
> the
> second MonthName is added.
> Thanks,
> Matt|||Thanks - that works Ok.
"Robert Bruckner [MSFT]" wrote:
> Does this work (by adding an explicit cast)?
> = IIF(Parameters!ReportPeriod.Value > 7,
> MonthName(Parameters!ReportPeriod.Value + 6),
> MonthName(CInt(Parameters!ReportPeriod.Value)))
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:E9371099-FDAB-4A79-95D5-8344C8595347@.microsoft.com...
> > Hi,
> > We are getting a runtime #error when using MonthName in an IIF function
> > more
> > than once as follows:
> >
> > IIF(Parameters!ReportPeriod.Value <
> > 7,MonthName(Parameters!ReportPeriod.Value +
> > 6),MonthName(Parameters!ReportPeriod.Value))
> >
> > Parameters!ReportPeriod is an integer, and the expression works Ok until
> > the
> > second MonthName is added.
> >
> > Thanks,
> > Matt
>
>sql
We are getting a runtime #error when using MonthName in an IIF function more
than once as follows:
IIF(Parameters!ReportPeriod.Value <
7,MonthName(Parameters!ReportPeriod.Value +
6),MonthName(Parameters!ReportPeriod.Value))
Parameters!ReportPeriod is an integer, and the expression works Ok until the
second MonthName is added.
Thanks,
MattDoes this work (by adding an explicit cast)?
= IIF(Parameters!ReportPeriod.Value > 7,
MonthName(Parameters!ReportPeriod.Value + 6),
MonthName(CInt(Parameters!ReportPeriod.Value)))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:E9371099-FDAB-4A79-95D5-8344C8595347@.microsoft.com...
> Hi,
> We are getting a runtime #error when using MonthName in an IIF function
> more
> than once as follows:
> IIF(Parameters!ReportPeriod.Value <
> 7,MonthName(Parameters!ReportPeriod.Value +
> 6),MonthName(Parameters!ReportPeriod.Value))
> Parameters!ReportPeriod is an integer, and the expression works Ok until
> the
> second MonthName is added.
> Thanks,
> Matt|||Thanks - that works Ok.
"Robert Bruckner [MSFT]" wrote:
> Does this work (by adding an explicit cast)?
> = IIF(Parameters!ReportPeriod.Value > 7,
> MonthName(Parameters!ReportPeriod.Value + 6),
> MonthName(CInt(Parameters!ReportPeriod.Value)))
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:E9371099-FDAB-4A79-95D5-8344C8595347@.microsoft.com...
> > Hi,
> > We are getting a runtime #error when using MonthName in an IIF function
> > more
> > than once as follows:
> >
> > IIF(Parameters!ReportPeriod.Value <
> > 7,MonthName(Parameters!ReportPeriod.Value +
> > 6),MonthName(Parameters!ReportPeriod.Value))
> >
> > Parameters!ReportPeriod is an integer, and the expression works Ok until
> > the
> > second MonthName is added.
> >
> > Thanks,
> > Matt
>
>sql
MonthName Chart Problem
I'm having a problem printing the name of the month on a monthly chart.
The data that I am attempting to chart includes a data point, a year (int, eg. 2006), and a month (int 1-12). A parameter is used to specify which months are included in the data. In some cases, we chart calendar years, in others we chart the previous 12 or 24 months. Each chart can include up to 4 years data. Each year is charted as a separate dynamic series. The data arrives sorted by year then month ascending.
When I chart calendar years, there's no problem. The data arrives sorted, and starts with January and continues through December.
The problem arises when I try to chart the Last 12 months. In this case, the data arrives sorted by year, then month - from June 2005 (6 / 2005) to May 2006 (5/2006). From June to December, the month names are printed correctly. However, from January to May, only numbers are printed.
The dataset code for the chart portion of the report follows below. I've honestly tried everything I can think of, including decoding and passing the full month name to the chart for labels. Your expert advice is very much appreciated.
Thanks!
Dataset Code:
<DataSetName>main</DataSetName>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="Years">
<GroupExpressions>
<GroupExpression>=Fields!YEAR.Value</GroupExpression></GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!YEAR.Value</SortExpression> <- Contains Year int
<Direction>Ascending</Direction>
</SortBy>
<SortBy>
<SortExpression>=Fields!TIME_DIMENSION.Value</SortExpression> <- Contains Month int
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Fields!YEAR.Value</Label>
</DynamicSeries>
</SeriesGrouping>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>=Fields!METRIC_NM.Value</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
...
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="Months">
<GroupExpressions>
<GroupExpression>=Fields!TIME_DIMENSION.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!TIME_DIMENSION.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=MonthName(Fields!TIME_DIMENSION.Value)</Label> <-The inconsistent month label
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
...
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!TIME_DIMENSION.Value</Value>
</DataValue>
<DataValue>
<Value>=Fields!METRIC_SUMMARY_VALUE.Value</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style>
<Format>#,##0.00</Format>
</Style>
<Value>=Fields!METRIC_SUMMARY_VALUE.Value</Value>
</DataLabel>
<Style>
...
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
...
</Chart>
I think this comes from the fact that you have a series grouping based on the year. You effectively have the first series for the months in the past year and the second series for the months of the current year:
2005: Jun Jul Aug Sep Oct Nov Dec (8) (9) (10) (11) (12)
2006: Jan Feb Mar Apr May
I bet you see the labels as shown in the "2005" line, right?
You can try removing the series grouping, and instead add two category groupings. The first category grouping is based on year, the second category grouping is based on the month.
-- Robert
|||
Thanks. You are of course entirely correct.
Eureka it works! The chart is alive. It's ALIVE!
MonthName Chart Problem
I'm having a problem printing the name of the month on a monthly chart.
The data that I am attempting to chart includes a data point, a year (int, eg. 2006), and a month (int 1-12). A parameter is used to specify which months are included in the data. In some cases, we chart calendar years, in others we chart the previous 12 or 24 months. Each chart can include up to 4 years data. Each year is charted as a separate dynamic series. The data arrives sorted by year then month ascending.
When I chart calendar years, there's no problem. The data arrives sorted, and starts with January and continues through December.
The problem arises when I try to chart the Last 12 months. In this case, the data arrives sorted by year, then month - from June 2005 (6 / 2005) to May 2006 (5/2006). From June to December, the month names are printed correctly. However, from January to May, only numbers are printed.
The dataset code for the chart portion of the report follows below. I've honestly tried everything I can think of, including decoding and passing the full month name to the chart for labels. Your expert advice is very much appreciated.
Thanks!
Dataset Code:
<DataSetName>main</DataSetName>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="Years">
<GroupExpressions>
<GroupExpression>=Fields!YEAR.Value</GroupExpression></GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!YEAR.Value</SortExpression> <- Contains Year int
<Direction>Ascending</Direction>
</SortBy>
<SortBy>
<SortExpression>=Fields!TIME_DIMENSION.Value</SortExpression> <- Contains Month int
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Fields!YEAR.Value</Label>
</DynamicSeries>
</SeriesGrouping>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>=Fields!METRIC_NM.Value</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
...
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="Months">
<GroupExpressions>
<GroupExpression>=Fields!TIME_DIMENSION.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!TIME_DIMENSION.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=MonthName(Fields!TIME_DIMENSION.Value)</Label> <-The inconsistent month label
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
...
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!TIME_DIMENSION.Value</Value>
</DataValue>
<DataValue>
<Value>=Fields!METRIC_SUMMARY_VALUE.Value</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style>
<Format>#,##0.00</Format>
</Style>
<Value>=Fields!METRIC_SUMMARY_VALUE.Value</Value>
</DataLabel>
<Style>
...
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
...
</Chart>
I think this comes from the fact that you have a series grouping based on the year. You effectively have the first series for the months in the past year and the second series for the months of the current year:
2005: Jun Jul Aug Sep Oct Nov Dec (8) (9) (10) (11) (12)
2006: Jan Feb Mar Apr May
I bet you see the labels as shown in the "2005" line, right?
You can try removing the series grouping, and instead add two category groupings. The first category grouping is based on year, the second category grouping is based on the month.
-- Robert
|||Thanks. You are of course entirely correct.
Eureka it works! The chart is alive. It's ALIVE!
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
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
>
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
>>
>>.
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
>>
>>.
Subscribe to:
Posts (Atom)