CODEWITHSUNDEEP
mysqlsql
user1830833
user1830833

Reputation: 181

Calculating Date from month and year

I have a table that contains two columns, expMonth and expYear. I need to SELECT * where the date is older than NOW(). My problem is, I don't even know how to approach this issue in SQL because I only have a month and year. Also, I would need to find the max amount of days for the given month and then based on that, piece together the information to create a date and then compare that date to NOW(). From what I have seen online, you can't really create a date in SQL from just having a month and year.

Example:

expMonth = 09 , expYear = 16.
findNumberOfDays(expMonth)
createDate(numberOfDaysforMonth,expMonth,expYear)`

SELECT * FROM Stacks WHERE createDate < NOW();

That was just some logical/pseudo code.

Does anyone have any suggestions on how to tackle this problem?

Upvotes: 0

Views: 59

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269447

Hmmm . . .

where str_to_date(concat(exp_year, '-', expmonth), '%Y-%m') < now()

MySQL can handle partial dates. But, if you really want, you could use 

str_to_date(concat(exp_year, '-', expmonth, '-01'), '%Y-%m-%d') < now()

if you think that is clearer.

Upvotes: 0

CptMisery
CptMisery

Reputation: 614

where str_to_date(concat(exp_year, '-', expmonth), '%Y-%m-01') < now()

If you need the date to be the last day of the month:

where last_day(str_to_date(concat(exp_year, '-', expmonth), '%Y-%m-01')) < now()

Upvotes: 0

Related Questions