Time and Date Datatypes and Functions in SQL

Time and date datatypes and functions in SQL  play very important role in DBMS (Database Management Systems). Such as, transaction management, or admission time of a student, time of product purchase and many more cases where time and date datatypes and functions becomes essential.

In this post, I will explain time and date datatypes and functions in SQL.

Time and Date Datatypes in SQL-

Date-

Date datatype can store data in yyyy-mm-dd format. For example, Ten January Twenty Twenty One would be written as 2021-01-10.

Time-

Time datatype can store data in hh:mm:ss format. For example, 03:35:45 where 03 is hour, 35 is minutes and 45 is seconds.

DateTime-

DateTime datatype can be thought as combination of date and time datatypes i.e. DateTime can have format of yyyy-mm-dd hh:mm:ss
for example, 2021-01-10 03:35:45

TimeStamp-

This is similar to DateTime datatype.

Year-

Year data type variable can store data in yyyy or yy format, for example 2021 or 21.

Time and Date Functions-

 

There are a lot of functions related to time and date, however, I will explain which are very important.

1- Now()  Function in SQL-

This function is very important and returns time stamp data from your system i.e. in format 2021-01-10 03:35:45.

2-

3-Curdate() Function in SQL-

This function returns current date of your system or server.

 

4- Date() Function in SQL-

Date() function extracts date part from timestamp data.

5- Month()  Function in SQL-

Month() function extracts month part from timestamp data.

6- Year()  Function in SQL-

Year() function extracts year part from timestamp data.

Use case of time and date datatype and function-

Suppose in a school you want to record date and time when a student come in school, i.e timestamp would be recorded.

Suppose you make three columns of a table ClassCS which are Name, regno, and TS so you will create a table in SQL as.

Create Table If Not Exists ClassCS(Name Varchar(20), regno int, TS TimeStamp);

Thereafter, you can insert first record as,

Insert Into ClassCS Values(“Manoj”, 1001, Now());

You can insert second record as,

Insert Into ClassCS Values(“Pappu”, 1002, Now());

You can insert third record as,

Insert Into ClassCS Values(“Sunil “, 1003, Now());

You can insert fourt record as,

Insert Into ClassCS Values(“Nitish”, 1004, Now());

 

And finally if you want to see all columns and rows you will use command.

select * from ClassCS;

See the result,

 

In result, you can that in TS column time stamp is different, and this is the time stamp when a record was inserted in a row.