Date functions in SQL

Date functions in SQL Introduction

For newcomers to SQL, dates are difficult since, while working with databases, the format of the date in the table must match the input date in order to insert. Datetime (time is also linked with date) is used in many contexts instead of date.

When working with dates, the most challenging element is ensuring that the format of the date you're trying to insert matches the format of the database's date column. we will be exploring a lot of Date functions in SQL further in the blog.

SQL Date Data Types

Before exploring Date functions in SQL first we need to understand the SQL date data types.

For storing a date or a date/time value in MySQL, the following data types are available:

• DATE - Date format in MYSQL is - YYYY-MM-DD
• DATETIME - Datetime format in MYSQL is -  YYYY-MM-DD HH:MI:SS
• TIMESTAMP - Timestamp format in MYSQL is -  YYYY-MM-DD HH:MI:SS
• YEAR - Year format in MYSQL is -  YYYY or YY

MYSQL

Default function to find a date in MYSQL:

NOW(): It returns the current date and time when you run this query
Code:
SELECT NOW();
Output
2022-03-20 13:42:24

CURDATE(): It returns the current date and time when you run this query
Code:
SELECT CURDATE();
Output
2022-03-20 13:42:24

CURTIME(): It returns the current time 
Code:
SELECT CURTIME();
Output
05:36:05

Date Format 

DATE_FORMAT(): This function Displays date/time data in different formats. This is one of the most used Date functions in SQL.

%a-This is Abbreviated as weekday name (Sun-Sat)

%b-This is Abbreviated as month name (Jan-Dec)

%c-This is Abbreviated as Month, numeric (0-12)

%D-This is Abbreviated as Day of month with English suffix (0th, 1st, 2nd, 3rd)

%d-This is Abbreviated as Day of month, numeric (00-31)

%e-This is Abbreviated as  Day of month, numeric (0-31)

%f-This is Abbreviated as Microseconds (000000-999999)

%H-This is Abbreviated as Hour (00-23)

%h-This is Abbreviated as Hour (01-12)

%I-This is Abbreviated as Hour (01-12)

%i-This is Abbreviated as Minutes, numeric (00-59)

%j-This is Abbreviated as Day of year (001-366)

%k-This is Abbreviated as Hour (0-23)

%l-This is Abbreviated as Hour (1-12)

%M-This is Abbreviated as Month name (January-December)

%m-This is Abbreviated as Month, numeric (00-12)

%p-This is Abbreviated as AM or PM

%r-This is Abbreviated as Time, 12-hour (hh:mm:ss followed by AM or PM)

%S-This is Abbreviated as Seconds (00-59)

%s-This is Abbreviated as Seconds (00-59)

%T-Time, 24-hour (hh:mm:ss)

%U-Week (00-53) where Sunday is the first day of week

%u-Week (00-53) where Monday is the first day of week

%V-Week (01-53) where Sunday is the first day of week, used with %X

%v-Week (01-53) where Monday is the first day of week, used with %x

%W-This is Abbreviated as Weekday name (Sunday-Saturday)

%w-This is Abbreviated as Day of the week (0=Sunday, 6=Saturday)

%X-Year for the week where Sunday is the first day of week, four digits, used with %V

%x-Year for the week where Monday is the first day of week, four digits, used with %v

%Y-This is Abbreviated as  Year, numeric, four digits

%y-This is Abbreviated as  Year, numeric, two digits