SQL ZERO TO HERO
DAY-22
ADVANCE SQL- Date Function
Created by:
Devikrishna R
Youtube - Vision Board: https://www.youtube.com/watch?v=EBJ5qfajbBw
LinkedIn - https://www.linkedin.com/in/devikrishna-r-
%F0%9F%87%AE%F0%9F%87%B3-92085678/
DATE FUNCTIONS
Fetch employee data who join on April Extract year, month, day, hour, minute, second
from date
SELECT * FROM employee WHERE EXTRACT(MONTH FROM join_date) = '04';
Fetch todays date To_Char convert number & date to a character string.
SELECT TO_CHAR(CURRENT_DATE, 'Month dd, yyyy') AS todays_date; todays_date October 10, 2022
SQL Dates
The most difficult part when working with dates is to be sure that the format
of the date you are trying to insert, matches the format of the date column in
the database.
As long as your data contains only the date portion, your queries will work as
expected. However, if a time portion is involved, it gets more complicated.
SQL Date Data Types
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
SQL Server 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
• SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
• TIMESTAMP - format: a unique number
Note: The date types are chosen for a column when you create a new table
in your database!
Sr.No. Function & Description
ADDDATE()
1
Adds dates
ADDTIME()
2
Adds time
CONVERT_TZ()
3
Converts from one timezone to another
CURDATE()
4
Returns the current date
CURRENT_DATE(), CURRENT_DATE
5
Synonyms for CURDATE()
CURRENT_TIME(), CURRENT_TIME
6
Synonyms for CURTIME()
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
7
Synonyms for NOW()
CURTIME()
8
Returns the current time
DATE_ADD()
9
Adds two dates
DATE_FORMAT()
10
Formats date as specified
11 DATE_SUB()
Subtracts two dates
DATE()
12
Extracts the date part of a date or datetime expression
DATEDIFF()
13
Subtracts two dates
DAY()
14
Synonym for DAYOFMONTH()
DAYNAME()
15
Returns the name of the weekday
DAYOFMONTH()
16
Returns the day of the month (1-31)
DAYOFWEEK()
17
Returns the weekday index of the argument
DAYOFYEAR()
18
Returns the day of the year (1-366)
EXTRACT
19
Extracts part of a date
FROM_DAYS()
20
Converts a day number to a date
FROM_UNIXTIME()
21
Formats date as a UNIX timestamp
22 HOUR()
Extracts the hour
LAST_DAY
23
Returns the last day of the month for the argument
LOCALTIME(), LOCALTIME
24
Synonym for NOW()
LOCALTIMESTAMP, LOCALTIMESTAMP()
25
Synonym for NOW()
MAKEDATE()
26
Creates a date from the year and day of year
MAKETIME
27
MAKETIME()
MICROSECOND()
28
Returns the microseconds from argument
MINUTE()
29
Returns the minute from the argument
MONTH()
30
Return the month from the date passed
MONTHNAME()
31
Returns the name of the month
NOW()
32
Returns the current date and time
33 PERIOD_ADD()
Adds a period to a year-month
PERIOD_DIFF()
34
Returns the number of months between periods
QUARTER()
35
Returns the quarter from a date argument
SEC_TO_TIME()
36
Converts seconds to 'HH:MM:SS' format
SECOND()
37
Returns the second (0-59)
STR_TO_DATE()
38
Converts a string to a date
SUBDATE()
39
When invoked with three arguments a synonym for DATE_SUB()
SUBTIME()
40
Subtracts times
SYSDATE()
41
Returns the time at which the function executes
TIME_FORMAT()
42
Formats as time
TIME_TO_SEC()
43
Returns the argument converted to seconds
44 TIME()
Extracts the time portion of the expression passed
TIMEDIFF()
45
Subtracts time
TIMESTAMP()
46 With a single argument this function returns the date or datetime expression. With tw
arguments
TIMESTAMPADD()
47
Adds an interval to a datetime expression
TIMESTAMPDIFF()
48
Subtracts an interval from a datetime expression
TO_DAYS()
49
Returns the date argument converted to days
UNIX_TIMESTAMP()
50
Returns a UNIX timestamp
UTC_DATE()
51
Returns the current UTC date
UTC_TIME()
52
Returns the current UTC time
UTC_TIMESTAMP()
53
Returns the current UTC date and time
WEEK()
54
Returns the week number
WEEKDAY()
55
Returns the weekday index
WEEKOFYEAR()
56
Returns the calendar week of the date (1-53)
YEAR()
57
Returns the year
58 YEARWEEK()
