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.
Search Keys
- sql datepart function
- datepart
- 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
- datepart date
- sql datepart minute
- ms sql datepart example
- sql datepart dw
- sql server datepart of datetime
- sql datepart format
- sql extract function
- extract year from date
- extract month from date
- extract function
- sql extract year
- sql extract date
- sql date extract
- extract time from date
- sql getdate
- sql server getdate
- getdate
- sql server datename
- sql server current date
- sql get date
- sql getdate format
- sql datename
- sql get date from datetime
- sql server sysdate
- select getdate
- 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 |