AppDividend
Latest Code Tutorials

SQL Date Functions Tutorial With Example | Date and Time in SQL

0

SQL Date Functions Tutorial With Example | Date and Time in SQL is today’s topic. In this section, we cover standard date functions in SQL. The different database system has different formats for date type data, and each RDBMS may employ different date functions, and there may also be differences in the syntax for each RDBMS even when the function call is same.

SQL Date Functions Tutorial With Example

MySQL comes with the following data types for storing a date or a date/time value in the database:

  • 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

LIST OF SQL DATE FUNCTIONS

#ADDDATE()

It returns a date after a certain time/date interval has been added.

select adddate("2019-06-09 02:52:47","7");

See the output.

SQL Date Functions Tutorial With Example

 

 

#ADDTIME()

It returns a time/date time after a certain time interval has been added.

select addtime("2019-06-09 02:52:47","2");

See the Output.

ADDTIME() in SQL

 

 

#CURDATE()

It returns the current date.

select curdate();

See the output.

CURDATE() in SQL

 

#CURRENT_DATE()

It returns the current date.

select current_date();

See the below output.

CURRENT_DATE()

 

#CURRENT_TIME()

It returns the current time.

select current_time();

See the below output.

CURRENT_TIME() in SQL

 

#CURRENT_TIMESTAMP()

It returns the current date and time.

select current_timestamp();

See the output.

CURRENT_TIMESTAMP() in SQL

 

#CURTIME() 

It returns the current time.

select curtime();

See the output.

CURTIME()  in SQL

 

#DATE()

It extracts the date value from a date-time expression.

select date("2019-06-10");

See the output.

DATE() in SQL

 

#DATEDIFF() 

It returns the difference in days between two date values.

select datediff("2019-06-16","2019-06-10");

See the output.

DATEDIFF() in SQL

 

#DATE_ADD() 

It returns a date after a certain time/date interval has been added.

select date_add("2019-06-05", INTERVAL 10 DAY);

See the following output.

DATE_ADD()  in SQL

 

#DATE_FORMAT() 

It formats a date as specified by a format mask.

select DATE_FORMAT("2018-06-15", "%Y");

See the following output.

DATE_FORMAT() in SQL

 

#DATE_SUB()

It returns a date after a certain time/date interval has been subtracted.

SELECT DATE_SUB("2017-06-15", INTERVAL 10 DAY);

See the following output.

DATE_SUB() in SQL

 

#DAY()

It returns the day portion of a date value.

SELECT DAY("2019-07-15");

See the output.

DAY() in SQL

 

#DAYNAME() 

It returns the weekday name for a date.

SELECT DAYNAME('2008-05-15');

See the output.

DAYNAME() in SQL

 

#DAYOFMONTH()

It returns the day portion of a date value.

SELECT DAYOFMONTH('2018-07-16');

See the output.

DAYOFMONTH() in SQL

 

#DAYWEEK()

It returns the weekday index for a date value.

SELECT WEEKDAY("2019-07-16");

See the output.

DAYWEEK() in SQL

 

#DAYOFYEAR()

It returns the day of the year for a date value.

SELECT DAYOFYEAR("2019-07-16");

See the output.

DAYOFYEAR() in SQL

 

#EXTRACT() 

It extracts parts from a date.

SELECT EXTRACT(MONTH FROM "2018-07-16");

See the output.

EXTRACT() in SQL

 

#FROM_DAYS()

It returns a date value from a numeric representation of the day.

select from_days(1234567);

See the output.

FROM_DAYS() in SQL

 

#HOUR()

It returns the hour portion of a date value.

select hour("2019-01-16 09:25:27");

See the output.

HOUR() in SQL

 

#LAST_DAY()

It returns the last day of the month for a given date.

select last_day('2019-01-25');

See the output.

LAST_DAY() in SQL

 

#LOCALTIME()

It returns the current date and time.

select localtime();

See the output.

LOCALTIME() in SQL

 

 

#LOCALTIMESTAMP()

It returns the current date and time.

select localtimestamp();

See the output.

LOCALTIMESTAMP() in SQL

 

 

#MAKEDATE()

It returns the date for a particular year.

select makedate(2007,128);

See the output.

MAKEDATE() in SQL

 

#MAKETIME()

It returns the time for a particular hour, minute, second combination.

select maketime(10,25,4);

See the output.

MAKETIME() in SQL

 

#MICROSECOND()

It returns the microsecond portion of a date value.

select microsecond("2019-06-19 09:10:45.000245");

See the output.

MICROSECOND() in SQL

 

#MINUTE()

It returns the minute portion of a date value.

select minute("2019-08-20 09:12:00");

See the output.

MINUTE() in SQL

 

#MONTH()

It returns the month portion of a date value.

select month('2019/01/15');

See the output.

MONTH() in SQL

 

#MONTHNAME() 

It returns the full month name for a date.

select monthname('2019/1/16');

See the output.

MONTHNAME() in SQL

 

#NOW() 

It returns the current date and time.

select now();

See the output.

NOW() in SQL

 

#PERIOD_ADD() 

It takes a period and adds a specified number of months to it.

select period_add(201803, 6);

See the output.

PERIOD_ADD() in SQL

 

#PERIOD_DIFF()

It returns the difference in months between two periods.

SELECT PERIOD_DIFF(201810, 201802);

See the output.

PERIOD_DIFF() in SQL

 

#QUARTER()

It returns the quarter portion of a date value.

SELECT QUARTER("2018/07/18");

See the output.

QUARTER() in SQL

 

#SECOND()

It returns the second portion of a date value.

SELECT SECOND("09:14:00:00032");

See the output.

SECOND() in SQL

 

#SEC_TO_TIME()

It converts numeric seconds into a time value.

SELECT SEC_TO_TIME(1);

See the output.

SELECT SEC_TO_TIME() in SQL

 

#STR_TO_DATE()

It takes a string and returns a date specified by a format mask.

SELECT STR_TO_DATE("JULY 18 2019", "%M %D %Y");

See the output.

STR_TO_DATE() in SQL

 

#SUBDATE() 

It returns a date after which a certain time/date interval has been subtracted.

SELECT SUBDATE("2019-06-15", INTERVAL 10 DAY);

See the output.

SUBDATE() in SQL

 

#SYSDATE()

 It returns the current date and time.

SELECT SYSDATE();

See the output.

SYSDATE() in SQL

 

#TIME()

It extracts the time value from a time/date time expression.

SELECT TIME("09:16:10");

See the output.

TIME() in SQL

 

#TIME_FORMAT()

It formats the time as specified by a format mask.

SELECT TIME_FORMAT("09:16:10", "%H %I %S");

See the output.

TIME_FORMAT() in SQL

 

#TIME_TO_SEC() 

It converts a time value into numeric seconds.

SELECT TIME_TO_SEC("09:16:10");

See the output.

TIME_TO_SEC() in SQL

 

#TIMEDIFF()

It returns the difference between two time/datetime values.

SELECT TIMEDIFF("09:16:10", "09:16:04");

See the output.

TIMEDIFF() in SQL

 

#TIMESTAMP()

 It converts an expression to a date-time value and if specified, adds an optional time interval to the value.

SELECT TIMESTAMP("2019-06-10", "08:16:10");

See the output.

TIMESTAMP() in SQL

 

#TO_DAYS()

It converts a date into numeric days.

SELECT TO_DAYS("2018-07-18");

See the output.

TO_DAYS() in SQL

 

#WEEK()

It returns the week portion of a date value.

SELECT WEEK("2018-06-18");

See the output.

WEEK() in SQL

 

#WEEKDAY()

It returns the weekday index for a date value.

SELECT WEEKDAY("2018-07-18");

See the output.

WEEKDAY() in SQL

 

#WEEKOFYEAR()

It returns the week of the year for a date value.

SELECT WEEKOFYEAR("2018-07-18");

See the output.

WEEKOFYEAR() in SQL

 

#YEAR() 

It returns the year portion of a date value.

SELECT YEAR("2019-07-18");

See the output.

YEAR() in SQL

 

#YEARWEEK()

It returns the year and week for a date value.

SELECT YEARWEEK("2019-06-18");

See the output.

YEARWEEK() in SQL

 

Finally, SQL Date Functions Tutorial With Example | Date and Time in SQL is over.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.