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

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×