CODEWITHSUNDEEP
phpmysqlsqldatedateadd
Stan
Stan

Reputation: 963

Convert month number to month name with sql

In my database the column "monthnumber" (varchar) indicates the month. (1 = january , 12 = december) Now I need to echo the name of the month instead of the number.

I already found some examples and it seems that using DateName( ) is the correct way to do this kind of stuff. But even with these examples, it's not working for me. Apparently I'm missing something but I can't figure out what...

.htaccess file:

RewriteRule ^example/([^/]+)/([^/]+)$ test?year=$1&monthnumber=$2 [L,QSA]

Html/php:

<?php
    $year= $_GET['year'];
    $monthnumber= $_GET['monthnumber'];

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

    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(":year", $year);
    $stmt->bindParam(":monthnumber", $monthnumber);
    $stmt->execute();

    if($result = $stmt->fetch(PDO::FETCH_ASSOC))
    {
    ?>

    <?php echo strtolower($result['monthname']);?> <?php echo $result['year'];?>

    <?php
    }// end if
    else {
    echo '0 results';
    }// end else
    ?>

Some row examples (year monthnumber)

(2014 8), (2014 7), (2013 8), ...

When I try to open example.com/2014/8, it gives me "0 results" instead of "August 2014", while my old code (month displayed as number) works:

$sql = "SELECT DISTINCT monthnumber, year FROM `exampletable` WHERE year = :year AND monthnumber = :monthnumber";

Upvotes: 0

Views: 12074

Answers (2)

vinod
vinod

Reputation: 2880

In MySql just use following statement to convert number to month (working example)

SELECT MONTHNAME(STR_TO_DATE(id,'%m')) as nameofmonth FROM `user`

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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.

Upvotes: 2

Related Questions