SQL date | Date Functions in SQL - sql - sql tutorial - learn sql




 date function in sql
  • In this section, we cover common date functions seen in SQL.
  • Different database systems have 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 the same.
  • All such differences are explained in each section.
  • Please note that we do not list all possible SQL date functions in this tutorial.
  • Rather, the most commonly used ones are covered.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

CURRENT_DATE ( )

  • Returns the current date.

CURRENT_TIME ()

  • Returns the current local time.

CURRENT_TIMESTAMP ()

  • Returns the current local date and local time as a timestamp value.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

CURDATE ( )

  • Returns the current date.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

CURTIME ( )

  • Returns the current local time.

DAYNAME (date_exp)

  • Returns a character string containing the data source/specific name of the day (for example, Sunday through Saturday).

DAYOFMONTH (date_exp)

  • Returns the day of the month based on the month field in date_exp as an integer value in the range of 1-31.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

DAYOFWEEK (date_exp)

  • Returns the day of the week based on the week field in date_exp as an integer value in the range of 1-7, where 1 represents Sunday.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

DAYOFYEAR(date_exp)

  • Returns the day of the year based on the year field in date_exp as an integer value in the range of 1-366.

EXTRACT (extract-field FROM extract-source)

  • Returns the extract-field portion of the extract-source. The extract-source argument is a datetime or interval expression.

HOUR (time_exp)

  • Returns the hour based on the hour field in time_exp as an integer value in the range of 0-23.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

MINUTE (time_exp)

  • Returns the minute based on the minute field in time_exp as an integer value in the range of 0-59.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

MONTH (date_exp)

  • Returns the month based on the month field in date_exp as an integer value in the range of 1-12.

MONTHNAME(date_exp)

  • Returns a character string containing the data source/specific name of the month (for example, January through December). Currently only supports English locale.

NOW ( )

  • Returns current date and time as a timestamp value.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

QUARTER(date_exp)

  • Returns the quarter in date_exp as an integer value in the range of 1-4, where 1 represents January 1 through March 31.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

SECOND (time_exp)

  • Returns the second based on the second field in time_exp as an integer value in the range of 0-59.

TIMESTAMPADD (interval, integer_exp, timestamp_exp)

  • Returns a timestamp offset by the specified interval.
  • Interval can be one of the following values
SQL_TSI_FRAC_SECOND (in billionths of a second)
SQL_TSI_SECOND
SQL_TSI_MINUTE
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR 
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Example:

  • SELECT timestampadd(SQL_TSI_HOUR, 3, {ts “1998-01-11 10:00:00”})
  • Returns: 1998-01-01 13:00:00
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Example:

  • SELECT timestampadd(SQL_TSI_DAY, -3, {ts “1998-01-11 10:00:00”})
  • Returns: 1998-01-08 10:00:00
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

TIMESTAMPDIFF (interval, timestamp_exp1, timestamp_exp2)

SQL date functions
  • Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1. Valid Intervals are described in the TIMESTAMPADD function.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Example:

  • SELECT timestampdiff(SQL_TSI_MONTH, {ts “1998-01-11 10:00:00”}, {ts “1998-03-11 10:00:00”})
  • Returns: -2
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Example:

  • SELECT timestampdiff( SQL_TSI_YEAR, {ts “2003-01-11 10:00:00”}, {ts “1998-01-11 10:00:00”})
  • Returns: 5
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

WEEK (date_exp)

  • Returns the week of the year based on the week field in date_exp as an integer value in the range of 1-53.
  • Returns the year based on the year field in date_exp as an integer value. The range is data source-dependent.

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Date Functions in SQL