CODEWITHSUNDEEP
mysqlsqldate
Bernat
Bernat

Reputation: 1556

Create Date from two cols in mySql

I have a table in my database with two columns: month (1-12) and year (yyyy).

I need to select records between two dates, for exemple

select * from Calendar a where SOMEDATE between STARTDATE and ENDDATE.

So the question is: how can I create the STARTDATE and the ENDDATE from this two columns I have?

Upvotes: 3

Views: 130

Answers (2)

Devart
Devart

Reputation: 121942

You can use this solution to make date from the year and month fields-

SELECT MAKEDATE(year, 1) + INTERVAL month - 1 MONTH FROM calendar;

The apply this one to WHERE condition, e.g. -

SELECT * FROM Calendar a
WHERE
  MAKEDATE(year, 1) + INTERVAL month - 1 MONTH BETWEEN
    @STARDATE - INTERVAL EXTRACT(DAY FROM @STARDATE) - 1 DAY
    AND
    @ENDDATE

But I have to ask you about the STARTDATE and ENDDATE criterias. What to do if STARTDATE is '2012-09-20'? Should the query return records with month = 9?

Upvotes: 0

davek
davek

Reputation: 22915

...where SOMEDATE between 
STR_TO_DATE(CONCAT_WS('-',STARTYEAR,STARTMONTH,1),'%Y-%m-%d')
and
DATE_SUB(
    STR_TO_DATE(CONCAT_WS('-',ENDYEAR,ENDMONTH + 1,1),'%Y-%m-%d')
    , INTERVAL DAY 1
)

Note that we convert both parts to type date, and use date_sub to subtract a single day from ENDMONTH + 1, since we don't know how many days there are in the relevant month.

Upvotes: 1

Related Questions