Use of DATES and its different built-in functions NOW(), FORMAT() in SQL

In this article, we are going to discuss about the use of DATES and its functions like now(), format() etc in SQL.
Submitted by Bharti Parmar, on January 29, 2019

Basically, when we are working with dates, we need to be sure that the format of the date, we are trying to insert in the database is in format, and matches the format of the date column in the database.

Sometimes, time gets complicated when it is involved in the database because our query will work accordingly as expected.

MySQL:

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

SQL Server:

DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: a unique number

SQL Dates pre-defined functions

Some pre-defined functions are also present in SQL for dates are:

NOW() and FORMAT() are scalar functions which return a single value, based in the input value.

NOW() function is used to Returns the current system date and time.

SYNTAX:

    SELECT NOW() FROM table_name;

FORMAT() function is used to Formats how a field is to be displayed.

SYNTAX:

    SELECT FORMAT(column_name,format) FROM table_name;

Column name and format parameter is required where format is used for specify the format.

Similarly, we use these built-in functions:

CURDATE() function is used to proceed the current date in MySQL.

CURTIME() function is used to take the current time in MySQL.

DATE() function is used to extracts the date part of a date or date/time expressionin MySQL.

EXTRACT() function is used to return a single part of a date and time in MySQL.

DATE_ADD() function is used to add a specified time interval to a datein MySQL.

DATE SUB() function is used to subtract a specified time interval from a datein MySQL.

DATEDIFF() function is used to returns the number of days between two datesin MySQL and In SQL server it is used to add or subtract a specified time interval from a date.

DATE FORMAT() function is used to display the date or time in different formats in SQL Server.

GETDATE() function is used to return the current date and time in SQL Server.

DATEPART() function is used to returns a single part of a date/time in SQL Server.

DATEADD() function is used to add or subtract a specified time interval from adate in SQL Server.

CONVERT() function is used to display the date or time in different formats in SQL Server.

Conclusion:

In this article, we have learn how to use DATES and its built-in functions in SQL? Have a great day! Happy Learning!

Comments and Discussions!

Load comments ↻

Top MCQs

Top Programs/Examples