ansaurus

Question

How to specify a relative date in MySQL?

Answer 1

+1 A:

UPD

STR_TO_DATE(CONCAT(YEAR(NOW()) + IF(MONTH(NOW()) = 12, 1, 0), '-', IF(MONTH(NOW()) = 12, 0, MONTH(NOW())) + 1, '-', 10), '%Y-%m-%d')

yes, it looks weird, but it solves "now is december" issue ;-)

zerkms 2010-09-29 05:35:58

Won't this break for December? And he was probably looking for a more general solution.

Alexander Sagen 2010-09-29 05:38:14

I'm not sure, i have no mysql to check right now. It would be great if somebody checks it.

zerkms 2010-09-29 05:39:06

`mysql> select STR_TO_DATE(CONCAT(YEAR(NOW()), '-', 13, '-', 10), '%Y-%m-%d');` returns NULL. 12 as month works.

Alexander Sagen 2010-09-29 06:02:32

You will need to feed a format to Str_to_date like this: Select Str_to_Date(CONCAT(YEAR(NOW()), '-', MONTH(NOW())+1, '-', 10), '%Y-%m-%d') - apart from this the query will work except for December, but I guess zerkms just wanted to show how this kind of calculation *could* be done.

faxi05 2010-09-29 06:13:09

@faxi05, yes, I forgot about second argument... Fixed, as well as "december issue"

zerkms 2010-09-29 06:16:43

Answer 2

A:

There's no such thing in MySQL, this has to be done before DB. At least with the flexibility you suggest when wanting generally "more eccentric".

Perhaps not a good idea to have that kind of logic in a query anyways? Depending on your case ofcourse.

Alexander Sagen 2010-09-29 05:39:25

Answer 3

A:

I'm going to start by quoting a comment from above, and saying I totally agree with it:

Did you literally want to find the 10th of next month? My assumption was you wanted some basic natural language processing, which is pretty much not doable in MySQL. – Alexander Sagen 6 hours ago

However, if you're absolutly, totally, desperate, then you could look at writing your own function to do this. You'd have to work out first exactly what sort of inputs you're going to send (i.e are you literally going to pass a single string value such as "10th of next month"), or can this be represented as day,month , so 10,+1 in the example. If it's the latter, it makes things a lot easier. Otherwise, you're looking at evaluating the text of the input and making decisions about what to do. Big IF statements, basically. It's doable - but it isn't what SQL was made for.

Tom Morgan 2010-09-29 13:12:26

Sorry for not being clear. I did simply want to get the date equivalent to (year)/(month+1)/10 , not parse a string. Thanks for covering all the bases though! I removed quotes to maybe make the question more clear.

Ricket 2010-09-29 14:53:08

Ah, it's easy then! You should be able to do it with DATEADD. You may still need to do it in a function - but it should be a case of adding the month, removing the same number of days as there are today, then adding as many days as you want to find. So for 1,10 (10th of next month)1) add 1 for the month to today = 29-10-2010, 2) remove (days in today=29) = 01-10-2010, 3) add 10 = 10-10-2010

Tom Morgan 2010-09-29 16:05:14

Answer 4

A:

For complex date logic you might want to consider pre-generating a date table and selecting from it. Having the appropriate columns will make selection with where clauses easier. Its a common and almost required technique for data warehouses. Something like this perhaps:

http://www.ipcdesigns.com/dim_date/

Then you can do (if I understood his schema correctly):

SELECT full_date FROM date_table WHERE date_table.day_num_of_month = 10 AND date_table.month_num_overall = (SELECT month_num_overall+1 FROM date_table WHERE full_date = NOW())

bot403 2010-09-29 15:42:52

ansaurus

tags:

views:

answers:

How to specify a relative date in MySQL?

related questions