sql dates code example

Example 1: sql date functions

ADDDATE Add a date interval (eg: 10 DAY) to a date (eg: 20/01/20) and return
the result (eg: 20/01/30).
ADDTIME Add a time interval (eg: 02:00) to a time or datetime (05:00) and
return the result (07:00).
CURDATE Get the current date.
CURRENT_DATE Same as CURDATE.
CURRENT_TIME Get the current time.
CURRENT_
TIMESTAMP Get the current date and time.
CURTIME Same as CURRENT_TIME.
DATE Extracts the date from a datetime expression.
DATEDIFF Returns the number of days between the 2 given dates.
DATE_ADD Same as ADDDATE.
DATE_FORMAT Formats the date to the given pattern.
DATE_SUB Subtract a date interval (eg: 10 DAY) to a date (eg: 20/01/20) and
return the result (eg: 20/01/10).
DAY Returns the day for the given date.
DAYNAME Returns the weekday name for the given date.
DAYOFWEEK Returns the index for the weekday for the given date.
DAYOFYEAR Returns the day of the year for the given date.
EXTRACT Extract from the date the given part (eg MONTH for 20/01/20 = 01).
FROM DAYS Return the date from the given numeric date value.
HOUR Return the hour from the given date.
LAST DAY Get the last day of the month for the given date.
LOCALTIME Gets the current local date and time.
LOCALTIMESTAMP Same as LOCALTIME.
MAKEDATE Creates a date and returns it, based on the given year and number of
days values.
MAKETIME Creates a time and returns it, based on the given hour, minute and
second values.
MICROSECOND Returns the microsecond of a given time or datetime.
MINUTE Returns the minute of the given time or datetime.
MONTH Returns the month of the given date.
MONTHNAME Returns the name of the month of the given date.
NOW Same as LOCALTIME.
PERIOD_ADD Adds the given number of months to the given period.
PERIOD_DIFF Returns the difference between 2 given periods.
QUARTER Returns the year quarter for the given date.
SECOND Returns the second of a given time or datetime.
SEC_TO_TIME Returns a time based on the given seconds.
STR_TO_DATE Creates a date and returns it based on the given string and format.
SUBDATE Same as DATE_SUB.
SUBTIME Subtracts a time interval (eg: 02:00) to a time or datetime (05:00)
and return the result (03:00).
SYSDATE Same as LOCALTIME.
TIME Returns the time from a given time or datetime.
TIME_FORMAT Returns the given time in the given format
TIME_TO_SEC Converts and returns a time into seconds.
TIMEDIFF Returns the difference between 2 given time/datetime expressions.
TIMESTAMP Returns the datetime value of the given date or datetime.
TO_DAYS Returns the total number of days that have passed from ‘00-00-
0000’ to the given date.
WEEK Returns the week number for the given date.
WEEKDAY Returns the weekday number for the given date.
WEEKOFYEAR Returns the week number for the given date.
YEAR Returns the year from the given date.
YEARWEEK Returns the year and week number for the given date.

Example 2: date datatype in sql

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