Sql query get month from datetime

Question

i've got a DB with invoices and datetime that they were created, i want to have a New columm with the name of The month according to The date of each invoice. I mean if The date is 2013-01-15, i would like to have " january" on The New columm. Thanks in advance, i've few knowledge about sql.

possible duplicate of [Get month from DATETIME in sqlite](http://stackoverflow.com/questions/650480/get-month-from-datetime-in-sqlite) — JustinDanielson, Jul 31 '13 at 23:07
Be sure to do a search before posting. A quick search for: "Sql query get month name from datetime" turns up many promising results, such as: [Returning month name in sql server query](http://stackoverflow.com/questions/5650830/returning-month-name-in-sql-server-query) . Obviously include your database type to narrow the results. — Leigh, Jul 31 '13 at 23:13

score 1 · Answer 1 · answered Jul 31 '13 at 23:06

1

If your database is MySQL, try:

DATE_FORMAT(date, '%M')

answered Jul 31 '13 at 23:06

Alex

11,451
6
37
52

score 1 · Answer 2 · answered Jul 31 '13 at 23:08

1

For MS SQL Server use

SELECT DATENAME(MONTH,invoiceDate)

answered Jul 31 '13 at 23:08

D Stanley

149,601
11
178
240

JustinDanielson · Answer 3 · 2013-07-31T23:16:20.670

Extract the month from the datetime object and then use it in a CASE statement.

Build upon this

select 
  case strftime('%m', date('now')) 
    when '01' then 'January' 
    when '02' then 'Febuary' 
    when '03' then 'March' 
    when '04' then 'April' 
    when '05' then 'May' 
    when '06' then 'June' 
    when '07' then 'July' 
    when '08' then 'August' 
    when '09' then 'September' 
    when '10' then 'October' 
    when '11' then 'November' 
    when '12' then 'December' else '' end
  as month

I would suggest copying the schema of your table, adding another column for the month. Then using the following statement.

INSERT INTO TABLE newTable (col1, col2, col3, ..., colLast, colMonth)
SELECT col1, col2, col3, ..., colLast, 
      case strftime('%m', date('now')) 
        when '01' then 'January' 
        when '02' then 'Febuary' 
        when '03' then 'March' 
        when '04' then 'April' 
        when '05' then 'May' 
        when '06' then 'June' 
        when '07' then 'July' 
        when '08' then 'August' 
        when '09' then 'September' 
        when '10' then 'October' 
        when '11' then 'November' 
        when '12' then 'December' else '' end
      as colMonth
FROM oldTable;

Then

drop table oldTable;

Then

Some alter to change the name of the new table to the name of the old table.

score 0 · Answer 4 · answered Jul 31 '13 at 23:12

0

In Oracle:

TO_CHAR(date, 'Month')

answered Jul 31 '13 at 23:12

PM 77-1

12,933
21
68
111

Sql query get month from datetime

4 Answers4