Printable View
Its known that
will display single row with head as fieldname and 1 as valueCode:SELECT 1 as head
Is there any way to generate multiple rows based on some range
For example i want to generate dates of particular month.
so it will be some 30 records generated without querying a particular table.
you could do a range quite easily by using a numbers table
but why did you say "without querying a table"?
I could use a number table, but was just trying my luck if it can be done without that.
I came across a post here
http://stackoverflow.com/questions/1...ween-two-dates
.Code:select a.Date
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2010-01-20' and '2010-01-24'
Trying to figure out how it works :).
meanwhile, you could also just create a numbers table that is large enough to cover the largest date range that you want to handle...Quote:
Originally Posted by tomalex0
then that query simplifies to --Code:CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY );
INSERT INTO numbers VALUES
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),...
Code:SELECT DATE('2011-02-01') -- start date
+ INTERVAL n DAY AS thedate
from numbers
WHERE DATE('2011-02-01') + INTERVAL n DAY
< DATE('2011-03-01') -- end date plus one
Hi that too worked, thanks
you understand the "last day plus one" combined with "less than" (instead of "less than or equal")?
it's so you don't have to bother figuring out feb 29
:)
Hi Sorry for the late reply
Yea got it :).
So it will be always better to end with Next month's firstDay. So we will never gonna miss 29 feb. Is that you were telling right?
One more question not regarding this
If you look into it, the time will be 00:00:00, is there anyway to get it as 23:59:59.Code:SELECT DATE_FORMAT(LAST_DAY(NOW()),"%Y-%m-%d %H:%i:%s")
Usually i used to concatenate.
no, it works just fine, the time is the same time as NOW() but the day is the last day of februaryQuote:
Originally Posted by tomalex0
but you do ~not~ want to use LAST_DAY in any range test like the one i showed you
I was just asking in general, if it is possible to get 23:59:59 with date using mysql function.
It will not be needed in the query that you provided.
i do not see why you would want to do that, and i strongly encourage you not to do that ...Quote:
Originally Posted by tomalex0
... but if you insist, you can try this --
DATE_FORMAT(
LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 SECOND
, '%Y-%m-%d %H:%i:%s')
Thanks for that, i probably won't try it then as you said.
I like the union-all method of creating "temporary tables".
The following should work:
Code:select x.Date
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) x
where x.Date between '2010-01-20' and '2010-01-24'
Quote:
Originally Posted by r937
Why not just:
CONCAT(CURRENT_DATE, ' 23:59:59')?
because it's very slow (converting a date into a string), and furthermore, leaves the result as a string, which, if you want to use it for anything (such as displaying it as a formatted datetime) has to first be converted into a datetime before the date formatting function converts it back into a stringQuote:
Originally Posted by transio
ewwww :injured:
and besides, using CURRENT_DATE instead of LAST_DAY(CURENT_DATE) puts you well outside of the required last day of the month on approximately 30 out of 31 days
I think it does have some limitation, for example if we want to find dates between 2010-01-20 and 2010-05-20, we will not get whole dates between them. There is some limit of 35 or 50 for that query. Otherwise we have to rewrite query a bit.Quote:
Originally Posted by transio
let's use the numbers table, you can easily have more than 50 rows...Quote:
Originally Posted by tomalex0
Yes thats right