Convert Month Number to Month Name Function in Sql

Convert Month Number to Month Name Function in SQL

A little hacky but should work:

SELECT DATENAME(month, DATEADD(month, @mydate-1, CAST('2008-01-01' AS datetime)))

Convert month number to month name with sql

This is your query:

SELECT DISTINCT DateName( month, DateAdd( month , 'monthnumber' , -1 ) ) AS monthname,
       monthnumber, year
FROM `exampletable`
WHERE year = :year AND monthnumber = :monthnumber

It is using functions from SQL Server, so I doubt this will work in MySQL.

You are better off just using a case statement:

select distinct (case when monthnumber = '1' then 'January'
                      when . . .
                      when monthnumber = '12' then 'December'
                 end) as monthname
from exampletable
where year = :year and monthnumber = :monthnumber;

You can also do this by constructing a date and using monthname():

select distinct monthname(date(concat_ws('-', year, monthnumber, '01'))) as monthname
from exampletable
where year = :year and monthnumber = :monthnumber;

Note: you should only use single quotes for date and string constants. Never use single quotes around column names, it will just lead to problems.

MySQL convert month number to month name

• We can convert the given input number to MySQL date format (focusing on month only), using Str_To_Date() function.
• Now, we simply need to use Monthname() function to extract the month name from the date.
• This will work only when NO_ZERO_DATE mode is disabled.

Try:

SET sql_mode = ''; -- disable NO_ZERO_DATE mode
SELECT MONTHNAME(STR_TO_DATE(1, '%m'));

---

As @Felk suggested in comments, if we need to get shortened month name, we can use Date_Format() function instead:

SET sql_mode = ''; -- disable NO_ZERO_DATE mode
SELECT DATE_FORMAT(STR_TO_DATE(1, '%m'), '%b');

---

If you don't want to disable the NO_ZERO_DATE mode, then you can create any random date using the month and call Monthname():

SELECT MONTHNAME(CONCAT('2018-',3,'-1')); -- 3 is the input number

Convert Month Number to Month Name Function on Access

In an Access query you should be able to use the MonthName function to convert a month number (e.g., 10) into a month name (e.g., 'October').

For example, if you have a query that returns a date of birth (DOB)...

SELECT [DOB] FROM [Clients] WHERE [ID]=1

DOB
----------
1977-05-15

.. you can return the name of the month in which they were born using

SELECT MonthName(Month([DOB])) AS MonthOfBirth FROM [Clients] WHERE [ID]=1

MonthOfBirth
------------
May

Converting month name to month number in SQL Server

Just another option is to try_convert() into a date

Note: the format() is optional

Example

Declare @YourTable Table ([Period_Name] varchar(50))  Insert Into @YourTable Values 
 ('Jan-19')
,('Feb-19')

Select * 
      ,NewVal = format(try_convert(date,'01-'+Period_Name),'MM')
 from @YourTable

Returns

Period_Name NewVal
Jan-19      01
Feb-19      02

Convert Month Name to Month Number

Assuming patientinfo.PatientDOB is a full & typed date, to get the integer month part of the date;

datepart(MM, patientinfo.PatientDOB)

or

month(patientinfo.PatientDOB)

wrapped in cast(?? as varchar(2)) for a character string.

Convert month number to month name from two tables

You have to use to_char() function with trim() function to get rid off extra spaces after month name:

select trim(to_char(sysdate, 'dd.Month')) || '.' || to_char(sysdate,'yy') from dual;

With your example:

select id, firstName, lastName, trim(to_char(date, 'dd.Month')) || '.' || to_char(date,'yy') 
from tableClient, tableActivity 
where tableClient.id = tableActivity.id and tableActivity.status= 'YES' 
AND tableActiviy.type = 'SMS';

Convert month name to month number in SQL Server

How about this?

select DATEPART(MM,'january 01 2011') -- returns 1
select DATEPART(MM,'march 01 2011')  -- returns 3
select DATEPART(MM,'august 01 2011') -- returns 8

---

Related Topics