CODEWITHSUNDEEP
mysql
Andi Giga
Andi Giga

Reputation: 4162

extract the month and the year in this format: "mm-yyyy" along with all columns

I have a field start in my databse from the type date. I want to extract the month and the year in this format: mm-yyyy, I also need all other columns.

I tried this:

$query='SELECT *, DATEADD(MONTH, DATEDIFF(MONTH, 0, start), 0) AS [start] FROM ...

according to Getting only Month and Year from SQL DATE

but it doesn't work. Error:

Incorrect parameter count in the call to native function 'DATEDIFF'"

Also I'm not sure how to continue afterwards. found this: SQL Query - Change date format in query to DD/MM/YYYY

So my guess would be:

Convert(varchar(10),CONVERT(date,DATEADD(MONTH, (DATEDIFF(MONTH, 0, start), 0) AS [start]),106),103)

but then I still have the days.

Upvotes: 3

Views: 6161

Answers (3)

Italo Borssatto
Italo Borssatto

Reputation: 15689

Use the DATE_FORMAT function.

SELECT DATE_FORMAT(start, '%m-%Y') FROM ...;

Upvotes: 4

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can use this logic as well:-

SELECT *, CONCAT(MONTH(start), '-',  YEAR(start)) AS START
FROM TABLE_NAME

Upvotes: -1

juergen d
juergen d

Reputation: 204794

You are using SQL-Server syntax for your MySQL installation. That won't work. Every DB engine has a slightly different syntax and functions. Use

SELECT *, year(start), month(start)
from your_table

Upvotes: 0

Related Questions