Kumpulan tanggal dari dua tanggal


Terkadang kita ingin melihat daftar tanggal dari range tanggal tertentu. Misalkan kita ingin menampilkan tanggal berapa saja yang ada pada range dari tanggal 20-02-2014 s/d 01-03-2014.

Hasil tampilan :

date_list

 

 

 

 

 

 

 

Dengan perintah SQL, kita dapat menuliskannya sebagai berikut :

    SELECT aDate FROM (
SELECT @maxDate – INTERVAL (a.a+(10*b.a)+(100*c.a)+(1000*d.a)) DAY aDate 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) a, /*10 day range*/
(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) b, /*100 day range*/
(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) c, /*1000 day range*/
(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) d, /*10000 day range*/
(SELECT @minDate := ‘2014-02-20’, @maxDate := ‘2014-03-01’) e
) f
WHERE aDate BETWEEN @minDate AND @maxDate
ORDER BY aDate

Pada bagian bawah query, masukkan tanggal awal pada @minDate dan tanggal akhir pada @maxDate

Terima kasih,

Semoga bermanfaat.

About blackphp

All about programming php, ajax, jquery, mysql, etc, tips n trick. Now, i'm concern in php programming with codeigniter framework n also training my design skill with photoshop or corel... wish me luck! ('o')

Posted on February 25, 2014, in mYsQL, MySQL Theory and tagged , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: