MySQL date range, case study

Just stumble upon situation where I need to find out if specific date in a date range.

I’ve try using lower than with greater than operator and use between in second attempt.

Quick mysql shell mode 🙂

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\alam>cd c:\appserv\mysql\bin

 

C:\AppServ\mysql\bin>mysql -uroot -p

Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 116
Server version: 5.0.45-community-nt-log MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> select curdate();

+————+
| curdate()  |
+————+
| 2008-02-11 |
+————+
1 row in set (0.00 sec)

mysql> use pendidikan;

Database changed

mysql> select * from stmik_periode_krs where curdate()>=tgl_mulai and curdate()
<=tgl_akhir and aktif=’Y’;

+—-+————+————+————+——-+————+————–
+———-+——-+
| id | tgl_mulai  | tgl_akhir  | petugas    | jenis | tgl_buka   | tahun_ajaran
| semester | aktif |
+—-+————+————+————+——-+————+————–
+———-+——-+
| 10 | 2008-02-11 | 2008-02-13 | sri_hayati | sp    | 2008-01-23 | 2007/2008
| 3        | Y     |
+—-+————+————+————+——-+————+————–
+———-+——-+
1 row in set (0.00 sec)

mysql>

mysql> select * from stmik_periode_krs where curdate() between tgl_mulai and tgl

_akhir and aktif=’Y’;

+—-+————+————+————+——-+————+————–
+———-+——-+
| id | tgl_mulai  | tgl_akhir  | petugas    | jenis | tgl_buka   | tahun_ajaran
| semester | aktif |
+—-+————+————+————+——-+————+————–
+———-+——-+
| 10 | 2008-02-11 | 2008-02-13 | sri_hayati | sp    | 2008-01-23 | 2007/2008
| 3        | Y     |
+—-+————+————+————+——-+————+————–
+———-+——-+
1 row in set (0.01 sec)

mysql>

Both work well, I just have to adjust my computer clock for trying the result.