EXTRACT() : Parts of Date & Time in MySQL

EXTRACT function to get part of date & time
SELECT EXTRACT(YEAR FROM '2017-03-08');
Output is 2017
Input format must be in "YYYY-MM-DD HH-MM-SS" , we can use STR_TO_DATE() for any other format ( See examples below )
SELECT EXTRACT( DAY FROM  '2017-04-28' );   
Output 28
SELECT EXTRACT( DAY FROM  '2017-04-28' );   
Output 28
SELECT EXTRACT( MONTH FROM  '2017-10-10' );
Output is 10
SELECT EXTRACT( YEAR_MONTH FROM  '2017-03-08' )
Output is 201703

EXTRACT() with STR_TO_DATE()

We can use other type input date formats also.
SELECT EXTRACT( YEAR FROM STR_TO_DATE('5-07-2017', '%d-%m-%Y') )

Read more on STR_TO_DATE()

EXTRACT() with NOW()

SELECT EXTRACT( YEAR FROM NOW() )
Output is Present Year 2017
SELECT EXTRACT( HOUR FROM NOW( ) )
Output is 12
SELECT EXTRACT( MINUTE FROM NOW( ) )
Output is 15
SELECT EXTRACT( SECOND FROM NOW( ) )
Output is 46
SELECT EXTRACT( MICROSECOND FROM '2017-02-11 22:39:59.000020' )
Ouput is 20
SELECT EXTRACT( HOUR_MINUTE FROM NOW( ) )
Output is 1215
SELECT EXTRACT( MINUTE_SECOND FROM NOW( ) )
Output is 1546
SELECT EXTRACT( SECOND_MICROSECOND FROM '2017-02-11 22:39:59.000020' ) //Output 59000020

Read more on NOW()

EXTRACT() with LOCALTIMESTAMP()

SELECT EXTRACT( YEAR FROM localtimestamp() )
SELECT EXTRACT( MONTH FROM localtimestamp() )
SELECT EXTRACT( HOUR_MINUTE FROM localtimestamp() )

EXTRACT() with CURDATE()

SELECT EXTRACT( DAY FROM CURDATE() )
Output is 12
SELECT EXTRACT( MONTH FROM CURDATE() )
Output is 12
SELECT EXTRACT( YEAR FROM CURDATE() )
Output is 2018

Read more on CURDATE()

Your Rating




Google+

Post Comment This is for short comments only. Use the forum for more discussions.








HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2018 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer