Scribd
Upload
13 views1 page

Date Functions

The document provides SQL commands for various date functions, including retrieving the current date and time, calculating the difference between dates, and formatting dates. It includes examples for finding specific components of a date, such as day, month, and year, as well as manipulating date values by adding intervals. Additionally, it demonstrates how to convert strings to date formats.

Uploaded by

Jagannath Manivannan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
13 views1 page

Date Functions

The document provides SQL commands for various date functions, including retrieving the current date and time, calculating the difference between dates, and formatting dates. It includes examples for finding specific components of a date, such as day, month, and year, as well as manipulating date values by adding intervals. Additionally, it demonstrates how to convert strings to date formats.

Uploaded by

Jagannath Manivannan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 1

-- DATE FUNCTIONS

-- -------------------------------------
-- [1] FIND CURRENT DATE AND TIME
-- SELECT NOW();

SELECT DATETIME('now');

-- [2] FIND CURRENT DATE


-- SELECT CURDATE();
SELECT DATE('now');

-- [3] FIND CURRENT TIME


-- SELECT CURTIME();
SELECT TIME('now');

-- [4] FIND THE NUMBER OF DAYS BETWEEN TWO DATE VALUES:


-- SELECT DATEDIFF(CURDATE(), '2022-01-01');
SELECT JULIANDAY('now') - JULIANDAY('2022-01-01');

-- [5] ADD A TIME/DATE INTERVAL TO A DATE


-- SELECT DATE_ADD( CURDATE(), INTERVAL 10 DAY);
SELECT DATE('now', '+10 days');
-- SELECT DATE_ADD( CURDATE(), INTERVAL 1 MONTH);
SELECT DATE('now', '+1 month');

-- [6] FIND DAY, MONTH & YEAR OF A DATE


-- SELECT DAY(CURDATE()), MONTH(CURDATE()), YEAR(CURDATE());
SELECT STRFTIME('%d', 'now'), STRFTIME('%m', 'now'), STRFTIME('%Y', 'now');

-- [7] FIND DAY NAME OF A DATE


-- SELECT DAYNAME(CURDATE());
SELECT STRFTIME('%w', 'now'); -- Returns 0-6 (Sunday is 0)

-- [8] FIND DAY OF THE YEAR FOR A DATE


-- SELECT DAYOFYEAR(CURDATE());
SELECT STRFTIME('%j', 'now');

-- [7] FIND DAY OF THE WEEK FOR A DATE


-- SELECT DAYOFWEEK(CURDATE());
SELECT STRFTIME('%w', 'now'); -- Returns 0-6 (Sunday is 0)

-- [8] FIND THE LAST DAY OF THE MONTH FOR A DATE


-- SELECT LAST_DAY(CURDATE()) ;
SELECT DATE('now', 'start of month', '+1 month', '-1 day');

-- [9] Date formatting


-- SELECT DATE_FORMAT(CURDATE(), '%d-%b-%Y') AS NEW_DATE;
SELECT STRFTIME('%d %m %Y', 'now') AS NEW_DATE;

-- [10] CONVERT STRING TO DATE FORMAT


-- SELECT STR_TO_DATE('12,10,2022','%d,%m,%Y');
SELECT DATE('2022-10-12');

You might also like