SQL DATE Functions

Function Parameters Result
SYSDATE no parameter returns current date and time of server
DATE(s) s = string of date returns the date part of the date & time expression
LAST_DAY(d) d = date returns the date of last day of month which has this date d
NEXT_DAY(d,day) d = date, day=day of week returns next day of date d
GREATEST(d1,d2,…dn) d1..dn=list of dates returns latest date from list
LEAST(d1,d2,…dn) d1..dn=list of dates returns oldest date from list
CURDATE(d) d=date returns current date
CURTIME(d) d=date returns current time
DATE_ADD(d, INTERVAL expr Type_value) d=date, expr = number of expression, Type_value = See the type_value table below Adds a specified time interval to a date
DATE_SUB(d, INTERVAL expr Type_value) d=date, expr = number of expression, Type_value = See the type_value table below Substracts a specified time interval to a date
DAYOFMONTH(d) d=date returns day of the month
DAYOFWEEK(d) d=date returns day of week , 1=Sunday, 2=Monday…7=Saturday
DAYOFYEAR(d) d=date returns day of year in the range 1 to 366
DAYNAME(d) d=date returns name of the weekday for that date
HOUR(t) t=time returns hour field of the specified time in the range 0-23
MINUTE(t) t=time returns minute field of the specified time in the range 0-59
SECOND(t) t=time returns second field of the specified time in the range 0-59
MONTH(d) d=date returns month field of the specified date in the range 0-12
QUARTER(d) d=date returns quarter of the year for date in the range 0-4
MONTHNAME(d) d=date returns month name of the specified date in the range 0-12
Type_Value
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
    Examples of Date Function

SELECT SYSDATE FROM DUAL; -> returns current date from server

SELECT HOUR(13:42:10); -> returns 13

SELECT DAYOFWEEK(2016-09-01); -> Returns 1

SELECT DAYOFYEAR(2016-09-01); -> 241

SELECT QUARTER(2016-09-01); -> 3