tomasd Posted November 29, 2007 Share Posted November 29, 2007 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? Quote Link to comment Share on other sites More sharing options...
fenway Posted November 29, 2007 Share Posted November 29, 2007 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' ); Quote Link to comment Share on other sites More sharing options...
tomasd Posted November 29, 2007 Author Share Posted November 29, 2007 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 | +------------+-------------+ Quote Link to comment Share on other sites More sharing options...
fenway Posted November 29, 2007 Share Posted November 29, 2007 OK... so what's the problem? Quote Link to comment Share on other sites More sharing options...
tomasd Posted November 29, 2007 Author Share Posted November 29, 2007 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) Quote Link to comment Share on other sites More sharing options...
obsidian Posted November 29, 2007 Share Posted November 29, 2007 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; Quote Link to comment Share on other sites More sharing options...
tomasd Posted November 29, 2007 Author Share Posted November 29, 2007 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 Quote Link to comment Share on other sites More sharing options...
obsidian Posted November 29, 2007 Share Posted November 29, 2007 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? Quote Link to comment Share on other sites More sharing options...
fenway Posted November 29, 2007 Share Posted November 29, 2007 Nor do I. Quote Link to comment Share on other sites More sharing options...
tomasd Posted November 30, 2007 Author Share Posted November 30, 2007 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted November 30, 2007 Share Posted November 30, 2007 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. Quote Link to comment Share on other sites More sharing options...
tomasd Posted November 30, 2007 Author Share Posted November 30, 2007 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted November 30, 2007 Share Posted November 30, 2007 OK... but fyi, it doesn't sound like you have a normalized db design, which may affect you in the long run. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.