mysql date problem

[tomasd]

Hi, I'm trying to select some data from my table using following:

mysql> SELECT out_date,Nov_29_2007 FROM MJV_STN where Nov_29_2007 < '10' and DAYNAME(out_date)='Saturday' || 'Sunday' ORDER BY out_date;
+------------+-------------+
| out_date   | Nov_29_2007 |
+------------+-------------+
| 2007-12-15 |        0.01 |
| 2007-12-22 |        0.01 |
| 2007-12-29 |        0.01 |
| 2008-01-19 |        0.01 |
| 2008-02-09 |        9.99 |
+------------+-------------+
5 rows in set, 1 warning (0.00 sec)

Now if try to automate the Nov_29_2007 using

mysql> SELECT DATE_FORMAT(NOW(), '%b_%d_%Y');
+--------------------------------+
| DATE_FORMAT(NOW(), '%b_%d_%Y') |
+--------------------------------+
| Nov_29_2007                    |
+--------------------------------+

So in theory I should be able to use

mysql> SELECT out_date,DATE_FORMAT(NOW(), '%b_%d_%Y') FROM MJV_STN where DAYNAME(out_date)='Saturday' || 'Sunday';
+------------+--------------------------------+
| out_date   | DATE_FORMAT(NOW(), '%b_%d_%Y') |
+------------+--------------------------------+
| 2007-08-11 | Nov_29_2007                    |
| 2007-08-11 | Nov_29_2007                    |
| 2007-08-18 | Nov_29_2007                    |
| 2007-08-18 | Nov_29_2007                    |
| 2007-08-25 | Nov_29_2007                    |
| 2007-08-25 | Nov_29_2007                    |
| 2007-09-01 | Nov_29_2007                    |
| 2007-09-01 | Nov_29_2007                    |
| 2007-09-08 | Nov_29_2007                    |
| 2007-09-08 | Nov_29_2007                    |
| 2007-09-15 | Nov_29_2007                    |
| 2007-09-15 | Nov_29_2007                    |
| 2007-09-22 | Nov_29_2007                    |
| 2007-09-22 | Nov_29_2007                    |
| 2007-09-29 | Nov_29_2007                    |
| 2007-09-29 | Nov_29_2007                    |
| 2007-10-06 | Nov_29_2007                    |
...snip...

I should be getting similar, but as you can see this is not the case, what am I doing wrong?

[fenway]

I'm confused about your first example... what's the second column?

 

And you can't use || like that... you need to say :

 

where DAYNAME(out_date)='Saturday' || DAYNAME(out_date) = 'Sunday';

 

or use an IN clause (preferably):

 

where DAYNAME(out_date) IN ( 'Saturday', 'Sunday' );

 

 

[tomasd]

Thanks for the suggestion, I'm using this now and it gives more results + no warnings

mysql> SELECT out_date,Nov_29_2007 FROM MJV_STN where Nov_29_2007 < '10' and DAYNAME(out_date) IN ( 'Saturday', 'Sunday' ) ORDER BY out_date;                +------------+-------------+
| out_date   | Nov_29_2007 |
+------------+-------------+
| 2007-12-15 |        0.01 |
| 2007-12-16 |        0.01 |
| 2007-12-22 |        0.01 |
| 2007-12-23 |        0.01 |
| 2007-12-29 |        0.01 |
| 2008-01-13 |        9.99 |
| 2008-01-19 |        0.01 |
| 2008-01-20 |        9.99 |
| 2008-01-27 |        9.99 |
| 2008-02-09 |        9.99 |
+------------+-------------+

[fenway]

OK... so what's the problem?

[tomasd]

OK... so what's the problem?

 

problem is that DATE_FORMAT(NOW(), '%b_%d_%Y') not recognized as a valid column description (Nov_29_2007)

mysql> SELECT out_date,DATE_FORMAT(NOW(), '%b_%d_%Y') FROM STN_KUN where Nov_29_2007 < '20' and DAYNAME(out_date) IN ( 'Saturday','Sunday' ) ORDER BY out_date;
+------------+--------------------------------+
| out_date   | DATE_FORMAT(NOW(), '%b_%d_%Y') |
+------------+--------------------------------+
| 2008-01-13 | Nov_29_2007                    |
| 2008-01-20 | Nov_29_2007                    |
| 2008-01-27 | Nov_29_2007                    |
| 2008-02-03 | Nov_29_2007                    |
| 2008-02-10 | Nov_29_2007                    |
| 2008-02-17 | Nov_29_2007                    |
| 2008-02-24 | Nov_29_2007                    |
| 2008-03-02 | Nov_29_2007                    |
| 2008-03-09 | Nov_29_2007                    |
| 2008-03-15 | Nov_29_2007                    |
| 2008-03-16 | Nov_29_2007                    |
| 2008-03-22 | Nov_29_2007                    |
| 2008-03-23 | Nov_29_2007                    |
| 2008-03-29 | Nov_29_2007                    |
| 2008-03-30 | Nov_29_2007                    |
| 2008-04-06 | Nov_29_2007                    |
| 2008-04-20 | Nov_29_2007                    |
| 2008-04-27 | Nov_29_2007                    |
| 2008-05-11 | Nov_29_2007                    |
| 2008-05-18 | Nov_29_2007                    |
+------------+--------------------------------+
20 rows in set (0.00 sec)

 

 

[obsidian]

So, alias it with AS:

 

SELECT out_date,DATE_FORMAT(NOW(), '%b_%d_%Y') AS formatted_date FROM STN_KUN where Nov_29_2007 < '20' and DAYNAME(out_date) IN ( 'Saturday','Sunday' ) ORDER BY out_date;

[tomasd]

So, alias it with AS:

 

SELECT out_date,DATE_FORMAT(NOW(), '%b_%d_%Y') AS formatted_date FROM STN_KUN where Nov_29_2007 < '20' and DAYNAME(out_date) IN ( 'Saturday','Sunday' ) ORDER BY out_date;

 

Thanks for the tip, however it still gives me following:

mysql> SELECT out_date,DATE_FORMAT(NOW(), '%b_%d_%Y') AS formatted_date FROM STN_KUN where Nov_29_2007 < '20' and DAYNAME(out_date) IN ( 'Saturday','Sunday' ) ORDER BY out_date;
+------------+----------------+
| out_date   | formatted_date |
+------------+----------------+
| 2008-01-13 | Nov_29_2007    |
| 2008-01-20 | Nov_29_2007    |
| 2008-01-27 | Nov_29_2007    |
| 2008-02-03 | Nov_29_2007    |
| 2008-02-10 | Nov_29_2007    |
| 2008-02-17 | Nov_29_2007    |
| 2008-02-24 | Nov_29_2007    |
| 2008-03-02 | Nov_29_2007    |
| 2008-03-09 | Nov_29_2007    |
| 2008-03-15 | Nov_29_2007    |
| 2008-03-16 | Nov_29_2007    |
| 2008-03-22 | Nov_29_2007    |
| 2008-03-23 | Nov_29_2007    |
| 2008-03-29 | Nov_29_2007    |
| 2008-03-30 | Nov_29_2007    |
| 2008-04-06 | Nov_29_2007    |
| 2008-04-20 | Nov_29_2007    |
| 2008-04-27 | Nov_29_2007    |
| 2008-05-11 | Nov_29_2007    |
| 2008-05-18 | Nov_29_2007    |
+------------+----------------+

 

I appreciate your help

[obsidian]

Thanks for the tip, however it still gives me following:

...

I appreciate your help

 

Not sure I understand the problem. To retrieve data from those results, you need to reference the formatted_date column. What isn't working?

[fenway]

Nor do I.

[tomasd]

Sorry for not expressing the problem clearly.

 

In the table I've got new columns added every day in Mon_DD_YYY format, and I need to extract it.

I'm trying to automate the date field, if I type it in manually like so:

 

SELECT out_date,Nov_29_2007

It works,

 

but then if I try to write it this way:

 

SELECT out_date,DATE_FORMAT(NOW(), '%b_%d_%Y')

it doesn't.

 

DATE_FORMAT(NOW(), '%b_%d_%Y') should be equal to Nov_29_2007  or whatever is the current date.

 

Thanks for your help guys.

[fenway]

I have no idea why you've chosen column names like that -- sounds like you're missing a table.  But AFAIK, you can't make column names dynamic -- and for good reason.

[tomasd]

I have no idea why you've chosen column names like that -- sounds like you're missing a table.  But AFAIK, you can't make column names dynamic -- and for good reason.

 

Thanks for explanation, I guess I just have to query dynamic column within php or something.

[fenway]

OK... but fyi, it doesn't sound like you have a normalized db design, which may affect you in the long run.