Formatting the now time

Not all functions have to be used with the SELECT statement. Often times functions that deal with time and date are used when creating a table. In this lesson we will look at the NOW(), CURDATE(), and CURTIME() functions when setting the current time/date in a record. We will also look at using the FORMAT() function to make the time/date stamp more readable.

NOW()

The NOW() function will return the current date and time.  The NOW() function returns a value of YYYY-MM-DD HH:MM:SS. For example, 2015-08-20 20:50:34.

Below is an example of using the NOW() function to enter a default value of the current date and time into a record when it is created.

CREATE TABLE student(
student_id  INT  PRIMARY KEY,
first_name  VARCHAR(30),
last_name  VARCHAR(30),
date_time DATETIME DEFAULT NOW()
);

CURDATE()

The CURDATE() function will return the current date. It returns a value of YYYY-MM-DD. For example, 2015-08-20.

Below is an example of using the CURDATE() function to enter a default value of the current date into a record when it is created.

CREATE TABLE student(
student_id  INT  PRIMARY KEY,
first_name  VARCHAR(30),
last_name  VARCHAR(30),
date DATE DEFAULT CURDATE()
);

CURTIME()

The CURTIME() function will return the current date. It returns a value of HH:MM:SS. For example, 20:50:34.

Below is an example of using the CURTIME() function to enter a default value of the current time into a record when it is created.

CREATE TABLE student(
student_id  INT  PRIMARY KEY,
first_name  VARCHAR(30),
last_name  VARCHAR(30),
time TIME DEFAULT CURTIME()
);

FORMAT()

You can format the results of the NOW() function using the FORMAT() function. The FORMAT() function takes two arguments, the field you wish to format, and the format pattern. Below is an example of the syntax.

SELECT FORMAT(field_name,format)
FROM table_name;

So if we want to only show the date from a field using the NOW() function we could write the query as:

SELECT FORMAT(time_date, YYYY-MM-DD)
FROM table_name;

Lesson Activities

  1. We have already created the student table and will need to alter the table to add a DATETIME field that uses NOW(). In the next lesson we will do this after we learn ALTER TABLE.

Questions