Can I use as in BETWEEN statement in mysql or something alike.
SELECT * FROM status WHERE date BETWEEN CONVERT_TZ('2013-11-01 00:00:00','GMT','UTC') AND CONVERT_TZ('2013-11-07 23:59:59','GMT','UTC') AS firstweek AND date BETWEEN CONVERT_TZ('2013-11-08 00:00:00','GMT','UTC') AND CONVERT_TZ('2013-11-15 23:59:59','GMT','UTC') AS secondweek AND date BETWEEN CONVERT_TZ('2013-11-16 00:00:00','GMT','UTC') AND CONVERT_TZ('2013-11-22 23:59:59','GMT','UTC') AS secondweek ORDER BY date DESC
I'm getting a syntax error.How can use an alias using BETWEEN? Is there other way for naming each week, this will serve as my identifier.
You could try
SELECT * FROM date JOIN
(SELECT CONVERT_TZ('2013-11-01 00:00:00','GMT','UTC') AS w1start,
CONVERT_TZ('2013-11-07 23:59:59','GMT','UTC') AS w1end,
CONVERT_TZ('2013-11-08 00:00:00','GMT','UTC') AS w2start,
CONVERT_TZ('2013-11-15 23:59:59','GMT','UTC') AS w2end,
CONVERT_TZ('2013-11-16 00:00:00','GMT','UTC') AS w3start,
CONVERT_TZ('2013-11-22 23:59:59','GMT','UTC') AS w3end) AS weeks
WHERE date BETWEEN w1start AND w1end
OR date BETWEEN w2start AND w2end
OR date BETWEEN w3start AND w3end
ORDER BY date DESC
This is at least a start.
If, however, you want to tell the application which of the 3 conditions is fulfilled, yiu might want to do
SELECT date BETWEEN w1start AND w1end AS firstweek,
date BETWEEN w2start AND w2end AS secondweek,
date BETWEEN w3start AND w3end AS thirdweek,
whatevercolumsyouneed
FROM date JOIN
(SELECT CONVERT_TZ('2013-11-01 00:00:00','GMT','UTC') AS w1start,
CONVERT_TZ('2013-11-07 23:59:59','GMT','UTC') AS w1end,
CONVERT_TZ('2013-11-08 00:00:00','GMT','UTC') AS w2start,
CONVERT_TZ('2013-11-15 23:59:59','GMT','UTC') AS w2end,
CONVERT_TZ('2013-11-16 00:00:00','GMT','UTC') AS w3start,
CONVERT_TZ('2013-11-22 23:59:59','GMT','UTC') AS w3end) AS weeks
HAVING firstweek OR secondweek OR thirdweek
ORDER BY date DESC
(untested; I am not sure if HAVING is placed correctly.)
You're putting an alias to the expression, not selected columns
You should make ir like this:
SELECT t.* FROM
(SELECT s.*, CONVERT(...) as field1, CONVERT(...) as field2 from status) t
...then do what you want with fieldsXX...
You're using AS in the WHERE clause. It can only be used in the column list and in the FROM clause.
SELECT a.somefield AS s, someexpr AS e
FROM table1 AS a, ...
WHERE ...