CODEWITHSUNDEEP
mysqldatemultiple-columns
Roni Tovi
Roni Tovi

Reputation: 886

How to generate DATE from 2 columns to use as selector in MySQL

I have month and year columns separately in a table, named m and y.

Now I want to select the records, for example between 2016-08 and 2018-10 (day is not important and that was the reason I have month and year columns)

So I'm looking for a command like below but no success, is there any MySQL function to achieve this?

SELECT * FROM mytable WHERE DATE(`y`,`m`) BETWEEN '2016-08-01' AND '2018-10-01'

Upvotes: 1

Views: 1310

Answers (2)

Joakim Danielson
Joakim Danielson

Reputation: 51973

You can do

CAST(CONCAT(y, '-', LPAD(m,2,'0'), '-', '01') as DATE)

That is create a string representation of a date, YYYY-MM-DD using CONCAT and then use CAST to convert the string into a date. LPAD is used to pad single digit months with a leading 0.

Upvotes: 0

Roni Tovi
Roni Tovi

Reputation: 886

Ok, I've found the solution myself.

I can extract the date part from two columns by using:

DATE(TIMESTAMP(CONCAT(`m`,'-',`y`,'-01')))

Upvotes: 2

Related Questions