A date is stored as one value, 2008-08-13, but questions are usually about a part of it: which month, which year, which day of the week. These functions pull one piece out — and three of them ask the server what time it is right now.
📝Who this page is for
Date functions are in the Informatics Practices (065) Class 12 syllabus, Unit 2, and not in the Computer Science (083) one. CS students may skip this page.
1Asking the server for the current time
Three functions take no argument at all. They report the moment the query ran, according to the server:
MySQL command line client
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2026-09-13 00:57:00 |
+---------------------+
1 row in set (0.00 sec)
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 00:57:00 |
+-----------+
1 row in set (0.00 sec)
MySQL command line client
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2026-09-13 |
+------------+
1 row in set (0.00 sec)
now()
Date AND time
2026-09-13 00:57:00
curdate()
Date only
2026-09-13
curtime()
Time only
00:57:00
💡Your answer will differ, and should
Run these and you will get your own date and time, not the ones above. That is the only place in this whole track where re-running a query is supposed to give a different answer.
2DATE() — throw the time away
MySQL command line client
mysql> select date("2050-07-25 10:43:14");
+-----------------------------+
| date("2050-07-25 10:43:14") |
+-----------------------------+
| 2050-07-25 |
+-----------------------------+
1 row in set (0.00 sec)
Given a value that carries a time as well, date() returns just the calendar part. It is what you would wrap around now() to get today's date — though curdate() already does that in one word.
3Pulling one piece out of a date
The remaining five all take a date and return one component of it. The same date is used throughout so the answers can be compared:
MySQL command line client
mysql> select year("2050-07-25");
+--------------------+
| year("2050-07-25") |
+--------------------+
| 2050 |
+--------------------+
1 row in set (0.00 sec)
mysql> select month("2050-07-25");
+---------------------+
| month("2050-07-25") |
+---------------------+
| 7 |
+---------------------+
1 row in set (0.00 sec)
mysql> select day("2050-07-25");
+-------------------+
| day("2050-07-25") |
+-------------------+
| 25 |
+-------------------+
1 row in set (0.00 sec)
Three numbers: the year, the month as a number, and the day of the month. Two more give words instead:
MySQL command line client
mysql> select monthname("2050-07-25");
+-------------------------+
| monthname("2050-07-25") |
+-------------------------+
| July |
+-------------------------+
1 row in set (0.00 sec)
mysql> select dayname("2050-07-25");
+-----------------------+
| dayname("2050-07-25") |
+-----------------------+
| Monday |
+-----------------------+
1 row in set (0.00 sec)
🔑Number or word — read the question
month() gives 7 and monthname() gives July. Likewise day() gives 25, the date in the month, while dayname() gives Monday, the day of the week. Those last two are the pair most often mixed up: one is a number up to 31, the other is a weekday.
4Using them on a column
Everything above used a literal date so the answers were easy to check. In practice you apply them to a column — here, extracting the birth year of every student:
MySQL command line client
mysql> select id, name, year(dob) from students order by dob;
+------+--------+-----------+
| id | name | year(dob) |
+------+--------+-----------+
| 1187 | Anjali | 2008 |
| 2500 | Bhim | 2008 |
| 2035 | Ravi | 2008 |
| 1250 | Sohan | 2008 |
| 2199 | Diana | 2009 |
| 2044 | Karma | 2009 |
| 3120 | Pema | 2010 |
| 1402 | Lhamu | 2010 |
| 1099 | Veena | 2010 |
| 3301 | Farhan | 2012 |
| 3612 | Nima | 2012 |
| 3033 | Tom | 2013 |
+------+--------+-----------+
12 rows in set (0.00 sec)
5In a where clause
This is where they become genuinely useful. “Everyone born in March” is impossible with a plain comparison, because the birth years differ — but easy once you extract the month:
MySQL command line client
mysql> select id, name from students where month(dob) = 3;
+------+--------+
| id | name |
+------+--------+
| 1187 | Anjali |
| 3120 | Pema |
| 3301 | Farhan |
+------+--------+
3 rows in set (0.00 sec)
Or by name, which reads better and gives the same three students:
MySQL command line client
mysql> select id, name from students where monthname(dob) = "March";
+------+--------+
| id | name |
+------+--------+
| 1187 | Anjali |
| 3120 | Pema |
| 3301 | Farhan |
+------+--------+
3 rows in set (0.00 sec)
Note the month name is quoted, because "March" is text, while 3 is a number and is not.
6All of them at a glance
Date functions
Function
Returns
For 2050-07-25
now()
Current date and time
2026-09-12 23:13:01
curdate()
Current date only
2026-09-12
curtime()
Current time only
23:13:01
date(v)
The date part of a value
2050-07-25
year(d)
The year, as a number
2050
month(d)
The month, as a number
7
monthname(d)
The month, as a word
July
day(d)
The day of the month
25
dayname(d)
The day of the week
Monday
7Recap
now / curdate / curtime
No argument. Date+time, date, time.
month vs monthname
7 versus July.
day vs dayname
25 versus Monday. The pair most often confused.
Useful in where
month(dob) = 3 finds a March birthday in any year.
✏️ Quick Check
What does select dayname('2050-07-25'); return?
✏️ Quick Check
Which function returns both the date and the time?
✏️ Quick Check
Which query finds students born in March, whatever the year?