SQL Date Functions | SQL Time Functions

Keeping you updated with latest technology trends, Join DataFlair on Telegram

1. SQL Date Functions

Today, in this SQL Date Functions tutorial, we will study the date and time functions in SQL. Moreover, we will discuss MySQL Date Functions, in which we will see SQL Server date Functions, MySQL Timestamp to Date, SQL Time Function. Also, we discuss SQL Date Functions data types and SQL working with Date.

So, let us start SQL Date Functions Tutorial.

SQL Date Functions

SQL Date Functions | SQL Time Functions

2. Date and Time Functions in SQL

There are many important SQL Date Functions, although it gets complicated with the SQL Time functions.
Let us study Date and Time Functions in SQL in detail.
Do you know about SQL Data Types

3. MySQL Date Functions

The following SQL Date Functions are important given in the table.

Function Description
NOW() This function is used to return the current date and time.
CURDATE() This function is used to return the current date.
CURTIME() This function is used to return the current time.
DATE() This function is used to extracts the date part of a date or date/time expression.
EXTRACT() This function is used to return a single part of a date/time.
DATE_ADD() This function is used to add a specified time interval to a date.
DATE_SUB() This function is used to subtract a specified time interval from a date.
DATEDIFF() This function is used to return the number of days between two dates.
DATE_FORMAT() This function is used to display date/time data in different formats.

Do you know about Null Values in SQL

a. SQL Server Date Functions

The following table shows the most important functions of date in SQL Server –

Function Description
GETDATE() Returns the current date and time.
DATEPART() Returns a single part of a date/time.
DATEADD() Adds or subtracts a specified time interval from a date.
DATEDIFF() Returns the time between two dates.
CONVERT() Displays date/time data in different formats.

b. SQL Date Data Types

There are the following data types in MySQL, which is used for a storing a data/time value in the database:
Have a look at SQL Injections

  • DATE – Format YYYY-MM-DD
  • DATETIME – Format: YYYY-MM-DD HH:MI:SS
  • TIMESTAMP – Format: YYYY-MM-DD HH:MI:SS
  • YEAR – Format YYYY or YY

There are the following data types in SQL Server Date, which is used for a storing a data/time value in the database:

  • SQL DATE – Format YYYY-MM-DD
  • DATETIME – Format: YYYY-MM-DD HH:MI:SS
  • SMALLDATETIME – Format: YYYY-MM-DD HH:MI:SS
  • SQL TIMESTAMP – Format: a unique number

Note: When we create a new table a data types are chosen for a new column in the database.  

c. SQL Working with Dates

Assume we have the following “Orders” table in SQL Dates:

OrderId ProductName OrderDate
1 Geitost 2008-11-11
2 Camembert Pierrot 2008-11-09
3 Mozzarella di Giovanni 2008-11-11
4 Mascarpone Fabioli 2008-10-29

Now when we want to select the records with an OrderDate of “2008-11-11” from the table above.
Let’s discuss SQL Server Transaction
We use the following SELECT statement:

SELECT * FROM Orders WHERE OrderDate='2008-11-11';

The following is the result for SQL Dates Functions

OrderId ProductName OrderDate
1 Geitost 2008-11-11
3 Mozzarella di Giovanni 2008-11-11

Now, assume that the “Orders” table looks like this (notice the time component in the “OrderDate” column):

OrderId ProductName OrderDate
1 Geitost 2008-11-11 13:23:44
2 Camembert Pierrot 2008-11-09 15:45:21
3 Mozzarella di Giovanni 2008-11-11 11:12:01
4 Mascarpone Fabioli 2008-10-29 14:56:59

If we use the same SELECT statement as above:

SELECT * FROM Orders WHERE OrderDate='2008-11-11';

You must know about SQL Operators
So, this was all in SQL Date Functions. Hope you like our explanation of SQL DateTime Functions.

4. Conclusion – SQL DateTime Functions

Hence, in this SQL Date Functions tutorial, we studied about the date and time functions in SQL. Moreover, we discussed MySQL Date Functions, in which we learned SQL Server date Functions, MySQL Timestamp to Date, SQL Time Function. Also, we saw SQL Date Functions data types and SQL working with Date. Still, if you have any query regarding SQL date Functions, you can ask in the comment tab.
See also –
SQL Auto Increment
For reference

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.