SQL DATEPART() | EXTRACT() Function


The SQL DATEPART() | EXTRACT() is a function, and returns a integer number containing the specified part of a given input date time value.

The SQL DATEPART() | EXTRACT() function when you are selecting portions of date or time values—such as just the month or day from a date.

The SQL DATEPART() | EXTRACT() function is supports only datetime or timestamp based table columns or fields.

It can be used in SELECT statement as well in where clause.

You can also search these topics using sql datepart function, sql server datepart, sql server datepart function, sql server datepart month, sql datepart week, sql server datepart weekday, sql server datepart example, sql datepart example, sql datepart minute, ms sql datepart example, sql server datepart of datetime, sql datepart format, sql extract function, extract year from date, extract month from date, extract time from date, sql server getdate, sql server datename, sql server current date, sql getdate format, sql get date from datetime, sql server sysdate, sql month from date, sysdate sql server, sql server get date only, sql server get year from date.

SQL DATEPART() | EXTRACT() Syntax

The below syntax is used to get specific portions of a date or time value from a given input datetime value.

For SQL SERVER


SELECT DATEPART(interval_datepart_value, 'valid_date_time_value');

For MS ACCESS


SELECT DATEPART('interval_datepart_value', 'valid_date_time_value');

For MySql


SELECT EXTRACT(interval_datepart_value FROM 'valid_date_time_value');


List Of Interval Parameters Of DatePart() | Extract() Function

Here, we have listed only few most important dateparts interval value for sql server, ms access, and mysql databases. Look the below table for dateparts interval values:

Interval Date Part Value
Description
SQL Server MS Access MySql
year, yy, yyyy yyyy year Year value
m, mm, month m month Month value
d, dd d day Day value
dy, y y Day of the year value
Hh h hour Hour of the day value
mi, n n minute Minute of the hour value
s, ss s second Second of the minute value

SQL DATEPART() | EXTRACT() Example - Using Interval DateParts Units

The following SQL SELECT statement will extract the day, month, and year from a given input datetime value. We use now() function to get the input date value.

For SQL Server


SELECT 
NOW() AS 'Current Date and Time', 
DATEPART(d, NOW()) AS 'Day',
DATEPART(m, NOW()) AS 'Month',
DATEPART(yyyy, NOW()) AS 'Year';

For MS Access


SELECT 
NOW() AS 'Current Date and Time', 
DATEPART('d', NOW()) AS 'Day',
DATEPART('m', NOW()) AS 'Month',
DATEPART('yyyy', NOW()) AS 'Year';

For MySql


SELECT 
NOW() AS 'Current Date and Time', 
EXTRACT(DAY FROM NOW()) AS 'Day',
EXTRACT(MONTH FROM NOW()) AS 'Month',
EXTRACT(YEAR FROM NOW()) AS 'Year';

The result of above query is:

Current Date and Time Day Month Year
13-05-2014 15:27:40 13 5 2014
You can also search these topics using current date sql server, getdate sql server format, sql server current datetime, sql server now date, convert datetime to date in sql, sql server convert datetime to date, sql getdate 1 day, sql select getdate, get day from date sql server, sql date functions, sql server date functions, sql datetime format, sql get current date, sql date comparison, sql server compare dates, sql server date functions examples, sql server date today, sql server date time functions, sql date operations, sql date query example, sql server date to string, time function sql, sql server format time, sql time datatype, sql date operators, sql query to display current date, sql get system date.