SQL DATEPART() | EXTRACT()

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.

Both the DATE PART() and EXTRACT() functions in PostgreSQL produce similar outcomes.

In actuality, the PostgreSQL DATE_PART() method replaces the EXTRACT() function. that is why their execution strategies are identical.

The DATE_PART() query is specific to PostgreSQL, although EXTRACT() really complies with the SQL standard. As a result, there is no overall performance difference. Therefore, the choice of function is entirely up to you. Use EXTRACT() if you want to adhere to SQL standards; otherwise, use DATE_PART().

In PostgreSQL, there is a small amount of code between DATE_PART() and EXTRACT(). Call the internal DATE_PART() function using the EXTRACT() syntax. Therefore, calling DATE_PART() directly should be a little faster if SQL portability is not an issue.

Syntax:

The syntax for the EXTRACT() function is as follows:

EXTRACT(field FROM source)

In the above syntax,

• It also returns a double precision type value as same as in the DATE_PART().
• There are two arguments as similar to the DATE_PART() function, field and source.
• The source can be a value of type TIMESTAMP or INTERVAL as similar as in DATE_PART(), and also the function casts the DATE value, if passed, to a TIMESTAMP value.
• And the field argument specifies the field to be extracted from the date or time value. The valid field values are same as in the DATE_PART().

Example: As we have done some of the examples of the DATE_PART() in the previous topic, so let’s do some examples of EXTRACT().

SELECT EXTRACT(YEAR FROM TIMESTAMP '2020-12-07 13:30:15');
SELECT EXTRACT(second FROM TIMESTAMP '2020-12-07 13:30:15');
SELECT EXTRACT(epoch FROM TIMESTAMP '2020-12-07 13:30:15');
SELECT EXTRACT(epoch FROM TIMESTAMP '2020-12-07 13:30:15') AS epoch;
SELECT EXTRACT(microseconds FROM TIMESTAMP '2020-12-07 13:30:15') AS Microseconds;
SELECT EXTRACT(microseconds FROM INTERVAL '21 years 11 months 7 days 15 hours 17 minutes 7 second') AS Quater;
SELECT EXTRACT(month FROM INTERVAL '21 years 11 months 7 days 15 hours 17 minutes 7 second') AS Month;

SQL, both the DATE_PART() function and EXTRACT() function gives similar results.

• The integer value of a specific datepart from the provided date is returned by the DATEPART () function.
• The int value is returned by this function. Datepart(datepart, date) requires two parameters, namely the datepart and the date.
• Day, month, and year are examples of dateparts.

We occasionally need to know the name or number of the day of the week. Several built-in procedures in SQL Server can be used to determine the day of the week from a given date. You can use the DATENAME function to obtain the day of the week's name, and the DATEPART function to obtain the day of the week's number.

Example 1: To Get The Name Of The Day Of Week using DATEPART function:

SELECT DATEPART(WEEKDAY, GETDATE())

Result:

Example 2: For a week (wk, ww) or weekday (dw) datepart, the DATEPART return value depends on the value set by SET DATEFIRST.

January 1 of any year defines the starting number for the week datepart. For example:

DATEPART (wk, 'Jan 1, xxxx') = 1

where xxxx is any year.

Example 3: This table shows the return value for the week and weekday datepart for '2007-04-21 ' for each SET DATEFIRST argument. January 1, 2007 falls on a Monday. April 21, 2007 falls on a Saturday.

For U.S. English,

SET DATEFIRST 7 -- ( Sunday )

Serves as the default. After setting DATEFIRST, use this suggested SQL statement for the datepart table values:

SELECT DATEPART(week, '2007-04-21 '), DATEPART(weekday, '2007-04-21 ')

The simplest way for two-digit minutes formatting with a sql example is needed by sql developers.

• In our sample sql script for formatting minutes, we'll use the DATEPART function.
• We may obtain the minutes value using DATEPART and the MI (Minutes) parameter.

Example 1: The output of the DATEPART function, which returns a number, had to be converted later into a string variable using the CONVERT or CAST string conversion functions.

The final step involves adding a zero string character and retrieving the RIGHT portion of the concatenated string through a string manipulation method.

DECLARE @Date DATETIME

SET @Date='20090909 09:00'
SELECT RIGHT('0' + CAST(DATEPART(MI,@date) AS Varchar(2)), 2)

SET @Date='20090909 09:09'
SELECT RIGHT('0' + CAST(DATEPART(MI,@date) AS Varchar(2)), 2)

SELECT RIGHT('0' + CAST(DATEPART(MI,GETDATE()) AS Varchar(2)), 2)

And the output of the above two digits minute format sql statement is as follows:

Example: Here is an illustration of how to extract the day of the week from a date using the extract() method.

The weekday is returned by dow as Sunday (0) to Saturday (6).

SELECT 
extract(dow from date '2020-12-27') AS "Day of week",
to_char(date '2020-12-27', 'Day') AS "Day Name";

Result:

So that it is clear which day is being returned, I also included the day's name in this example.

A TIMESTAMP column's HOUR and MINUTE components should be extracted. Oracle provides the functions EXTRACT(HOUR FROM...) and EXTRACT(MINUTE FROM...).

When retrieving the HOUR and MINUTE components of a date and time in SQL Server, we can use the DATEPART() function. In this case, we must specify the datepart argument of the DATEPART function as either hour or hh and mi.

Example 1: This is an example using SYSTIMESTAMP:

SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) AS CURRENT_HOUR FROM DUAL;

SELECT EXTRACT(MINUTE FROM SYSTIMESTAMP) AS CURRENT_MINUTE FROM DUAL;

Example 2: To separate a date column's HOUR and MINUTE components. Oracle offers the TO_CHAR(date, ‘HH24') and TO_CHAR(date,’MI’) functions.

This is an example using SYSDATE:

SELECT TO_CHAR(SYSDATE,'HH24') AS CURRENT_HOUR FROM DUAL;

SELECT TO_CHAR(SYSDATE,'MI') AS CURRENT_MINUTE FROM DUAL;

Example 3: The time (hour, minute or second) components can be found by either:

Using CAST( datevalue AS TIMESTAMP ) to convert the DATE to a TIMESTAMP and then using EXTRACT([ HOUR | MINUTE]FROM timestampvalue );

Using TO_CHAR( datevalue, format_model ) to get the value as a string.

For example:

SELECT EXTRACT( HOUR   FROM CAST( datetime AS TIMESTAMP ) ) AS Hours,
       EXTRACT( MINUTE FROM CAST( datetime AS TIMESTAMP ) ) AS Minutes
FROM   (
  SELECT TO_DATE( '2016-01-01 09:42:01', 'YYYY-MM-DD HH24:MI:SS' ) AS datetime FROM DUAL
);

Output:

Example 4: Time in the 24-hour format hh:miAM (or PM):

SELECT  GETDATE() 'Today',
    CONVERT(VARCHAR(5), GETDATE(), 108) + 
    (CASE WHEN DATEPART(HOUR, GETDATE()) > 12 THEN 'PM'
        ELSE 'AM'
    END) 'hh:miAM (or PM)'

Example 5: Several experienced developers have expressed confusion about using SQL Server to find time using solely datetime datatypes. Let's take a cursory look at the answer. Let's see how to extract the date portion just from a datetime as well as how to extract the time in hour:minute format from a datetime.

SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinute,
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
GO

SELECT
CONVERT(TIME,GETDATE()) AS HourMinuteSecond,
CONVERT(DATE,GETDATE(),101) AS DateOnly
GO

Example 1: Extract ISO week:

ISOWEEK: returns the date expression's ISO 8601 week number. On Monday, ISOWEEKS begin. The range of return values is [1, 53]. The Monday preceding the first Thursday of the Gregorian calendar year marks the start of the first ISOWEEK of each ISO year.

WITH table AS (SELECT DATE('2017-11-05') AS date)
SELECT
  date,
  EXTRACT(WEEK(SUNDAY) FROM date) AS week_sunday,
  EXTRACT(WEEK(MONDAY) FROM date) AS week_monday FROM table;

Output:

Example 2: Without extract:

Here are the examples to extract the week number from a date in SQL Server.

DECLARE @date date = '2021-01-01';
SELECT DATEPART(iso_week, @date) AS 'ISO WEEK NUMBER';

Output:

Example 3: Comparison with Gregorian Year:

Although the date in the illustration above is January 1, 2021, you will observe that the output really refers to the 53rd week of 2020 according to ISO.

To compare ISO WEEK with Gregorian week, let's look at another example:

DECLARE @date date = '2021-01-01';
SELECT
  DATEPART(week, @date) AS 'GREGORIAN WEEK' ,
  DATEPART(iso_week, @date) AS 'ISO WEEK';

Output :

As you can see from the aforementioned example, the week number for the same date can vary depending on whether you're using the Gregorian calendar or the ISO Date and Time standard.

1. Extract Month

The EXTRACT() function can be used to extract the day of the month from a given date.

The following illustrates the syntax:

EXTRACT(DAY FROM date)

The EXTRACT() function receives the date that you want to extract the day of the month from using this syntax. Any legitimate date literal or expression that evaluates to a date value can be used as the date.

The day of the month of the input date is represented by a number in the return value of the EXTRACT() method.

Example 1: The method for determining the day of the month from the date of August 21st, 2018, is demonstrated in the example below:

SELECT EXTRACT(MONTH FROM '2018-08-21')

Output:

Example 2: When using timestamp or date values, day returns the day of the month (1 – 31).

SELECT extract(
    day from date '2020-12-27'
    ) AS "Day of month";

Result:

2. Datepart month

For the purpose of returning precise dates, the DATEPART() function was created.

The weekday or month name cannot be extracted from the date because the DATEPART() function only provides an integer as a result. You can use the DATENAME() or FORMAT() functions for that, so don't worry.

Use the SQL DATEPART function to return the date part for a date, such as the day, month, year, hour, minute, second, the day of the year, etc.

Syntax:

The DATEPART function takes two arguments,

SELECT DATEPART(month, GETDATE());

The syntax above will extract the month number from the current date of the system.

Example 1: Therefore, we can use this function to return exactly the same result as the previous example:

DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT     
    DATEPART(month, @date) AS MONTH,
From Result;

Result:

Using this function has the advantage of allowing you to return additional date and time components. As you can see from this illustration, I returned both the day and the weekday (day is the day of the month, weekday is the day of the week). Additionally, you can return different time units, like minutes, seconds, milliseconds, etc.

Example 2: To extract the month from a date, you use the following statement:

SELECT DATEPART(month, '2018-07-21 15:30:20.05') month;

Output:

Example 3: When a date is supplied in SQL, the DATEPART function returns an integer, such as 4 for April, 25 for the day, etc.

The DATEPART thus returns an integer value. Therefore, for the supplied date, it gives the month number rather than the name.

See a query and output for the month part:

SELECT DATEPART(month, '25 April 2017') AS "Month Number"

Output:

Given month names, sort the rows by month number (you want January to be shown first, December last).

By integrating the two operations, the following formula can be used to obtain the month as a number:

EXTRACT(MONTH FROM TO_DATE(birthday_month, 'Month'))

Put this expression after ORDER BY to sort the rows by month.

Example 1: The birthday table contains two columns: name and birthday_month. The months are given in names, not in numbers.

You want to sort the rows by birthday_month.

SELECT *
FROM birthday
ORDER BY EXTRACT(MONTH FROM TO_DATE(birthday_month, 'Month'));

The result looks like this (the rows are sorted in ascending order by birthday_month):

Discussion:

You must convert the month to a number (for example, "January" becomes 1, "February" becomes 2, etc.) in order to sort the rows by month beginning in January and ending in December. If not, "December" would appear before "January." The function TO DATE(birthday month, "Month") changes a whole month name to a date in the format "0001-MM-01." You might receive "0001-12-01" for December, for instance.

The month can now be extracted from this date value using the EXTRACT(MONTH FROM date) function. The month will be one of the ranges of 1 to 12.

Example 2: If you'd like to see the latest month first, you'll need to sort in descending order. To do this, you need to use a DESC keyword, like this:

SELECT *
FROM birthday
ORDER BY EXTRACT(MONTH FROM TO_DATE(birthday_month, 'Month')) DESC;

Keep in mind that NULLs are shown first when sorting in descending order in PostgreSQL and last when sorting in ascending order in Oracle. Additionally, the rows with identical birthday_month are shown in a random sequence (you may see Angie Julia second and Narelle Dillan third, or Narelle Dillan second and Angie Julia third).

Datepart Month Name

Like the DATEPART() method, the DATENAME() function returns the name of the provided date part instead of the date part itself (but only where a name is applicable). That indicates that, if necessary, it returns the weekday name or the month name.

Example 1: Here’s how to get the month name with this function:

DECLARE @date datetime2 = '2018-07-01';
SELECT DATENAME(month, @date) AS Result;

Result:

Example 2: To obtain the month name from a date in SQL Server, we can use the DATENAME() function. In this case, we must provide the datepart argument of the DATENAME function as month, mm, or m, all of which will produce the same outcome.

SELECT GETDATE() 'Today', DATENAME(month,GETDATE()) 'Month Name'
SELECT GetDate() 'Today', DATENAME(mm,GETDATE()) 'Month Name'
SELECT GetDate() 'Today', DATENAME(m,GETDATE()) 'Month Name'

Result:

Example 3: You can use DATEPART() function to extract the month number from the month name in date:

SELECT DATEPART (MM, 'January 01 2020') - returns 1
SELECT DATEPART (MM, ‘April 01 2020') - returns 4
SELECT DATEPART (MM, 'May 01 2020')  - returns 5

There is no built-in function for that if your column only contains the month and you want to convert it to a month number. To change month name to month, use the SQL statement below:

CASE WHEN MonthName= 'January' THEN 1
     WHEN MonthName = 'February' THEN 2
     ...
     WHEN MonthName = 'December' TNEN 12
END AS MonthNumber

EXTRACT() is not supported by SQL Server. If you're using SQL Server, you may extract the day of the month from a date using the DAY() or DATEPART() functions.

Example 1: For example, both statements below return the day of the current date in SQL Server:

SELECT DAY(CURRENT_TIMESTAMP);

SELECT DATEPART(DAY, CURRENT_TIMESTAMP);

MySQL offers the DAY() method, which returns the day of the month from a date in addition to the EXTRACT() function.

Example 2: To get the day of the current date, you use the CURRENT_TIMESTAMP function in the EXTRACT() function as follows:

SELECT EXTRACT(DAY FROM CURRENT_TIMESTAMP)

Note that the EXTRACT() function is a SQL standard function supported by MySQL, Oracle, and PostgreSQL.

Example 3: SQLite does not support EXTRACT(), DAY() or DATEPART() function. To extract a month from a date, you can use the strftime() function as follows:

SELECT strftime('%d', CURRENT_TIMESTAMP)

Example 4: All of the parts used with DATE and DATETIME (as described above), as well as the following, can be part values when expression is a TIMESTAMP:

You can add an optional argument to expression when it's a TIMESTAMP to set the output timezone:

WITH table AS (
  SELECT
    CAST('2021-01-05 00:00:00+00' AS TIMESTAMP) AS datetime
)
SELECT
  datetime,
  EXTRACT(HOUR FROM datetime AT TIME ZONE 'America/Los_Angeles') AS hour_in_LA,
  EXTRACT(HOUR FROM datetime) AS hour
FROM
  table;

Output:

Use the EXTRACT() function to extract YEAR, MONTH, and DAY from a DATE value.

Example 1: The EXTRACT function can be used to extract YEAR and MONTH together. The YEAR MONTH must be supplied as an argument to this function. Consider the following function, which utilises information from table "Collegedetail," to help you comprehend it:

Select EXTRACT(YEAR_MONTH From estb) from student;

Output:

Example 2: A quick tutorial on how to extract the year and month from a date column in MySQL is provided below. We will use the EXTRACT function, which enables us to extract specific portions of a date, to accomplish this.

As you can see, the table has three columns:

• The Primary Key id.
• The name of the member.
• A date column called date_registered.

What if we wanted to group the members by the year and month they enrolled and count how many people signed up? In this instance, the Year and Month may be obtained from the date_registered column using the EXTRACT function.

SELECT 
    EXTRACT(YEAR_MONTH FROM date_registered) AS year_month_registered, 
    COUNT(*) AS num_registered
FROM `members`
GROUP BY year_month_registered

As you can see, the Year and Month has been stored in a YYYYMM format in an alias called “year_month_registered.”

Example 3: The following example extracts the value of the YEAR field from a DATE value.

SELECT
  EXTRACT( YEAR FROM TO_DATE( '31-Dec-1999 15:30:20 ',  'DD-Mon-YYYY HH24:MI:SS' ) ) YEAR
FROM
  DUAL;

In this example, we used the TO_DATE() function to convert a date literal to a DATE value.

Output:

Example 4: Extracting month from a date:

Similarly, you can extract the values of other fields as shown below:

SELECT
  EXTRACT( MONTH FROM TO_DATE( '31-Dec-1999 15:30:20 ',  'DD-Mon-YYYY HH24:MI:SS' ) ) MONTH
FROM
  DUAL;

Result:

This article demonstrates how to retrieve simply the time portion of a DateTime in Microsoft SQL Server. Sometimes, when developing, we only need to take the Time component of the entire Datetime.

Example 1: If the given date value is 2017-01-01 09:32:15.000, we wish to retrieve only the time component of that date, which is the result "09:32." Therefore, this is an element of server-side coding that most developers use, such as split functions, etc. However, we can show how to use a straightforward query in MS SQL SERVER to extract the time component from a DateTime.

select GETDATE() as default_date,
cast(DATEPART(HOUR,GETDATE()) as varchar(2))+':'+ 
cast(DATEPART(MINUTE,GETDATE()) as varchar(4)) as time_part

Example 2: In MS SQL server, get the Time part from the Datetime. Here, we extracted the hours and minutes using the MS SQL datepart() function before casting the results as varchar and concatenating them to extract only the time portion of the provided date.

A single component of a date or time, such as the year, month, day, hour, or minute, can be returned using the DATEPART() function.

We obtain the time component from DateTime in MS SQL Server 2008 by using the following query:

select GETDATE() default_date,
cast(GETDATE() as time) time_part;

1. HOUR part of the DateTime in Sql Server

To obtain the HOUR portion of a datetime in SQL Server, we can use the DATEPART() function. In this case, the datepart argument of the DATEPART function must be specified as hour or hh.

Example:

SELECT GETDATE() 'Today', DATEPART(hour,GETDATE()) 'Hour Part'
SELECT GETDATE() 'Today', DATEPART(hh,GETDATE()) 'Hour Part'

Result:

2. MINUTE part of the DateTime in Sql Server

In order to obtain the MINUTE portion of a DateTime in SQL Server, we can use the DATEPART() function. In this case, the datepart argument of the DATEPART function must be specified as minute, mi, or n.

Example:

SELECT GETDATE() 'Today', 
DATEPART(minute,GETDATE()) 'Minute Part'
SELECT GETDATE() 'Today', DATEPART(mi,GETDATE()) 'Minute Part'
SELECT GETDATE() 'Today', DATEPART(n,GETDATE()) 'Minute Part'

Result:

3. SECOND part of the DateTime in Sql Server

To obtain the SECOND part of a DateTime in SQL Server, we can use the DATEPART() function. In this case, we must specify the datepart argument of the DATEPART function as second, ss, or s.

Example:

SELECT GETDATE() 'Today', 
DATEPART(second,GETDATE()) 'Second Part'
SELECT GETDATE() 'Today', DATEPART(ss,GETDATE()) 'Second Part'
SELECT GETDATE() 'Today', DATEPART(s,GETDATE()) 'Second Part'

Result:

Accessing to the date, time, timestamp, and interval elements of temporal data types is made possible through SQL extract.

The keyword from is used in SQL extract to separate the field name from the value.

EXTRACT(< field> FROM < expression>)

You cannot include the field names in single or double quotes because they are also SQL keywords.

An precise numeric value is returned by a SQL extract. It also includes fractions for the second.

The extract fields specified by the SQL standard are listed in the following table.

1. Related Features

Extract can only get single fields.

To extract the full date (year, month, day) or time (hour, minute, second) from a timestamp, cast can be used:

CAST(<timestamp> AS [DATE|TIME])

Especially with the group by clause, this is helpful. It is frequently the incorrect option in the where clause. See "Inappropriate Use of The Where Clause" below for further information on this.

It is standard procedure to utilise the proprietary trunc function to set all time fields to zero (0) in order to use the date only—without time components:

trunc(<timestamp>)

Note that the result still has the time components—they are just set to zero.

The effect is basically like the following cast expression in standard SQL:

CAST(CAST(<timestamp> AS DATE) AS TIMESTAMP)

Compatibility: SQL extract is now a part of the optional feature F052, "Intervals and datetime arithmetic," which was previously available in SQL-92 (intermediate). Considering its maturity and usefulness, not all significant databases yet offer extract.

2. Related Anti-Patterns

String Formatting Functions: Using string formatting routines (such as to char) in place of extract to obtain individual date or time data is a very common anti-pattern. Based on the current locale, these string functions frequently apply undesired formatting, such as leading spaces or zeros, or a comma (,) instead of a period (.), as the decimal point.

This behaviour that is dependent on the environment can result in defects that are difficult to fix because they don't manifest in all environments.

Example 1: Consider the following anti-pattern:

WHERE EXTRACT(YEAR FROM some_date) = 2016

It is common practise to avoid mentioning the "final minute of" the important time period by using this anti-pattern.

Given that it is actually impossible to pinpoint the "final moment of," this is a crucial and desirable objective.

3. Time units are not uniform

It is common knowledge that a month's length varies. At least in part, the laws governing leap years are known. Any "final instant of" might be calculated algorithmically just based on these factors.

However, there are also irregular leap seconds. They are periodically included upon request. As an illustration, the final UTC second of 2016 was 23:59:60. A day may not end at 23:59:59 UTC if you use that time as your end time. 3

It is hard to predict the final minute of a month for more than six months in advance due to the irregularity and the very short lead time when it comes to leap seconds insertions—the 2016 leap second was revealed less than six months in advance.

In addition to this more or less theoretic special case, it is also good to avoid the need to specify the “last moment of” because it is rather awkward to calculate.

The time component’s resolution is unknown (at least in the future).

Even if you have accurately identified the last day and the last second of a period, you might still need to specify the "last instant of" that period with a sufficient number of fractional digits. If you are aware that the appropriate column's type (such as timestamp(0)) forbids fractions, you don't need to think about any fractions at this time. But odds are that the "latest moment of" assumptions are not updated if the type is later changed to timestamp(6).

Therefore, it is wise to refrain from using the phrase "final moment of." The incorrect strategy for achieving that goal is to use extract, cast, or string formatting functions.

Example 1: The following where clause is equivalent to the extract example from above and still avoids specifying the “last moment of” the year 2016:

WHERE some_date >= DATE'2016-01-01'
  AND some_date <  DATE'2017-01-01'

Note the pattern: For the lower bound, use an inclusive comparison (>=), whereas for the upper bound, use an excluding comparison (<). As a result, you must provide the upper bound as the first moment to be excluded from the result. By employing the less problematic "first moment of" twice, the inclusive/exclusive pattern eliminates the requirement to describe the "final moment of" the pertinent time period.

Keep in mind that since between contains both boundary values, SQL's between cannot be utilized for this pattern.

Compared to the extract solution, the inclusive/exclusive condition has two advantages:

• It works for arbitrary time frames.
• You can easily select a single month, day, …—even if it is not aligned to the calendar.

Example 2: Consider how you would implement the following example using extract, to_char, or something similar:

WHERE some_date >= DATE'1994-03-11'
  AND some_date <  DATE'1995-03-11'

It can use an index on the date/time column

If the where clause surrounds the indexed columns through a function or expression like extract, then an index on some date is largely worthless. 5 A pattern that explicitly includes and excludes can employ such an index.

Extensions that are proprietary: Additional Fields.

A few databases allow for additional extract fields. The main popular proprietary extract fields are listed in the table below. Please be aware that these are proprietary extensions and that their behaviour may vary from one product to the next. For instance, the field week operates in each of the three tested databases, but it yields a different outcome for each one.

4. Proprietary Alternatives

The majority of databases have sufficient functionality to produce the same outcome as the typical extract expression. The proprietary substitute is listed below for databases that do not (completely) support extract.

datepart: SQL SERVER

Microsoft SQL Server offers the proprietary datepart function. The following example is equivalent to extract(year from < datetime>).

DATEPART(year, < datetime>)

The return type is always an integer. Fractions of seconds can be retrieved as separate fields (e.g. millisecond).

Example 1: The following expression behaves like extract(second from < datetime>) with up to nine fractional digits:

DATEPART(second , <datetime>) 
+ CAST(DATEPART(nanosecond, <datetime>)
AS NUMERIC(9,0)
)/power(10,9)

See “DATEPART (Transact-SQL)” for the full list of available fields.

5. Strftime — SQLite

The strftime function in SQLite allows for the formatting of dates and times as strings. 6 Simply format that component and cast it to a numeric type if necessary to extract a single component. The next illustration is the same as extract(year from "").

CAST(STRFTIME ('%Y', <datetime>) AS NUMERIC)

Note that the format string '%S' (for seconds) does not include fractions. Use '%f' instead (seconds including three fractional digits):

CAST(STRFTIME ('%f', <datetime>) AS NUMERIC)
extract(second_microsecond …) — MySQL, MariaDB

Example 1: MySQL’s extract and MariaDB’s extract both always return integer values. To get the seconds with fractions, use the proprietary second_microsecond extract field:

EXTRACT(second_microsecond FROM <datetime>)/power(10,6)

An integer reflecting the specified datepart of the supplied date is returned by this function.

Syntax:

DATEPART ( datepart , date )  

Arguments:

datepart: The specific portion of the date input that DATEPART will return an integer for. Each valid datepart argument is listed in this table.

Note: DATEPART does not accept user-defined variable equivalents for the datepart arguments.

ARGUMENTS

date: An expression that resolves to one of the following data types:

• date
• datetime
• datetimeoffset
• datetime2
• smalldatetime
• time

DATEPART will support a column expression, expression, user-defined variable, or string literal for dates. To prevent confusion problems, use four-digit years. For details on two-digit years, see Configure the Two Digit Year Cutoff Server Configuration Option.

Return Type: int

Return Value: Each datepart and its abbreviations return the same value.

Example 1: The datepart inputs for the command SELECT DATEPART(datepart,'2007-10-30 12:15:32.1234567 +05:10') are included in this table along with the appropriate return values. The date parameter has a data type of datetimeoffset(7). The nanosecond datepart return value always has 00 in the last two locations and a scale factor of 9:

.123456700

Example 2: For a week (wk, ww) or weekday (dw) datepart, the DATEPART return value depends on the value set by SET DATEFIRST.

January 1 of any year defines the starting number for the week datepart.

For example:

DATEPART (wk, 'Jan 1, xxxx') = 1

where xxxx is any year.

Example 3: The week and weekday return values for the datepart "2007-04-21" are displayed in this table for each SET DATEFIRST input. The first day of 2007 is a Monday. The 21st of April 2007 is a Saturday. For American English,

SET DATEFIRST 7 -- ( Sunday )

serves as the default.

Example 4: After setting DATEFIRST, use this suggested SQL statement for the datepart table values:

SELECT DATEPART(week, '2007-04-21 '), DATEPART(weekday, '2007-04-21 ')

1. year, month, and day datepart Arguments

The values that are returned for DATEPART (year, date), DATEPART (month, date), and DATEPART (day, date) are the same as those returned by the functions YEAR, MONTH, and DAY, respectively.

2.iso_week datepart

The ISO week-date system, which assigns numbers to each week, is part of ISO 8601. Every week has a connection to the year that Thursday falls on. For instance, week one of 2004 (2004W01) ran from Sunday, January 4, 2004, through Monday, December 29, 2003. This method of numbering is commonly utilized in European nations and areas. Most non-European nations and regions do not utilise it.

Note that 52 or 53 could be the highest week number in a year.

It's possible that different nations' and regions' numbering systems don't adhere to the ISO standard. This table displays six options:

ISO_WEEK DATEPART

First day of week First week of year contains Weeks assigned two times Used by/in Sunday 1 January, First Saturday, 1-7 days of year: Yes United States

Monday 1 January, First Sunday, 1-7 days of year: Yes

Most of Europe and the United Kingdom Monday 4 January, First Thursday.

4-7 days of year No ISO 8601, Norway, and Sweden.

Monday 7 January, First Monday, 7 days of year No

Wednesday 1 January, First Tuesday, 1-7 days of year Yes

Saturday 1 January, First Friday, 1-7 days of year Yes

3. tzoffset

Example 1: DATEPART returns the tzoffset (tz) value as the number of minutes (signed). This statement returns a time zone offset of 310 minutes:

SELECT DATEPART (tzoffset, '2007-05-10  00:00:01.1234567 +05:10');

DATEPART renders the tzoffset value as follows:

• Tzoffset returns the time offset in minutes for datetimeoffset and datetime2, with the offset for datetime2 always being 0 minutes.
• DATEPART returns the time offset in minutes for data types that can automatically convert to datetimeoffset or datetime2. Other date and time data types are an exception.
• All other types of parameters give an error.

4. smalldatetime date Argument

Smalldatetime date values have seconds returned by DATEPART as 00.

Default for a datepart that is not in a date argument, returned.

Only when a literal is supplied for date will DATEPART return the default for that datepart if the date argument data type lacks the requested datepart.

Example 1: For example, the default year-month-day for any date data type is 1900-01-01. This statement has date part arguments for datepart, a time argument for date, and it returns 1900, 1, 1, 1, 2.

SELECT DATEPART(year, '12:10:30.123')  
    ,DATEPART(month, '12:10:30.123')  
    ,DATEPART(day, '12:10:30.123')  
    ,DATEPART(dayofyear, '12:10:30.123')  
    ,DATEPART(weekday, '12:10:30.123');  

Example 2: Date will yield error 9810 if it is supplied as a variable or table column and the data type for that variable or column lacks the required datepart. Variable @t in this illustration has a time data type. The date component year is incorrect for the time data type, hence the instance fails.

DECLARE @t time = '12:10:30.123';   
SELECT DATEPART(year, @t);  

Example 3: Fractional seconds

These statements show that DATEPART returns fractional seconds:

SELECT DATEPART(millisecond, '00:00:01.1234567'); -- Returns 123  
SELECT DATEPART(microsecond, '00:00:01.1234567'); -- Returns 123456  
SELECT DATEPART(nanosecond,  '00:00:01.1234567'); -- Returns 123456700  

Remarks:

The select list, where, having, group by, and order by clauses all support the usage of datepart.

In SQL Server 2019, DATEPART implicitly converts string literals to datetime2 types (15.x). This indicates that when the date is supplied as a string, DATENAME does not accept the format YDM. To use the YDM format, you must explicitly cast the string into a datetime or smalldatetime type.

Example 4: The base year is returned in this example. Date calculations are made easier with the base year. The date in the example is indicated by a number. It is important to note that SQL Server understands 0 as January 1, 1900.

SELECT DATEPART(year, 0), DATEPART(month, 0), DATEPART(day, 0);  

Output:

Example 5: This example returns the day part of the date 12/20/1974.

Uses AdventureWorks:

SELECT TOP(1) DATEPART (day,'12/20/1974') FROM dbo.DimCustomer;  

Output:

Example 6: This example returns the year part of the date 12/20/1974.

Uses AdventureWorks:

SELECT TOP(1) DATEPART (year,'12/20/1974') FROM dbo.DimCustomer;

Output:

Following demos shows how to get some of the commonly required Time Part format from a DateTime.

Example 1: Time in the 24-hour format hh:mi:ss:

SELECT GETDATE() 'Today', 
CONVERT(VARCHAR(8), GETDATE(), 108) 'hh:mi:ss'

Output:

Example 2: Time in the 24-hour format hh:mi:ss:mmm :

SELECT GETDATE() 'Today',
CONVERT(VARCHAR(12),GETDATE(),114) 'hh:mi:ss:mmm';

Output:

Example 3: Time in the 12-hour format hh:mi:ss:mmmAM (or PM):

SELECT  GETDATE() 'Today',
RIGHT(CONVERT(VARCHAR(26), GETDATE(), 109),14) 'hh:mi:ss:mmmAM (or PM)'

Output:

Example 4: Time in the 12-hour format hh:miAM (or PM):

SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7))

Output:

Example 5: Time in the 24-hour format hh:miAM (or PM):

SELECT  GETDATE() 'Today',
    CONVERT(VARCHAR(5), GETDATE(), 108) + 
    (CASE WHEN DATEPART(HOUR, GETDATE()) > 12 THEN 'PM'
        ELSE 'AM'
    END) 'hh:miAM (or PM)'

The SQL DATEPART() method provides an integer value that represents a particular portion of the DATE or TIMESTAMP expression, such as the year, month, week, day, or hour. An input date and the name of the portion that has to be extracted are the two arguments used by the function.

However, only SQL Server, Oracle, and Azure SQL databases support the datepart() function. We can use features like EXTRACT for different database management servers like PostgreSQL and MYSQL ().

Syntax:

The following shows the syntax of the DATEPART() function:

DATEPART ( date_part , input_date )

The DATEPART() takes two arguments:

• date_part is the part of a date to be extracted.
• input_date is the date from which the date part is extracted.

Date, time, datetime, datetimeoffset, datetime2, smalldatetime So, make sure that the date expression mentioned by you is the incorrect format.

Return Value: Datepart function returns an integer value representing the extracted part. For example, DATEPART(month, ‘2020/06/19’) will return 6 to represent JUNE.

Example 1: The following example uses the DATEPART() function to query the gross sales by year, quarter, month and day.

SELECT DATEPART(year, shipped_date) [year], 
   DATEPART(quarter, shipped_date) [quarter], 
   DATEPART(month, shipped_date) [month], 
   DATEPART(day, shipped_date) [day], 
   SUM(quantity * list_price) gross_sales
FROM sales.orders o
   INNER JOIN sales.order_items i ON i.order_id = o.order_id
WHERE shipped_date IS NOT NULL
GROUP BY DATEPART(year, shipped_date), 
   DATEPART(quarter, shipped_date), 
   DATEPART(month, shipped_date), 
   DATEPART(day, shipped_date)
ORDER BY [year] DESC,

SQL Server DATEPART Function with table column example:

In this illustration, we extracted the year, quarter, month, and day from the data in the shipped date column using the DATEPART() function. We combined the gross sales (quantity * list price) by these date ranges in the GROUP BY clause.

Keep in mind that the SELECT, WHERE, HAVING, GROUP BY, and ORDER BY clauses all support the use of the DATEPART() function.

Example 2: Extracting year from a given date:

SELECT DATEPART(yy, '2020/05/18') AS 'Year as part of Date';

Example 3: Extracting the week’s number from a given date (considering year as a whole):

SELECT DATEPART(WEEK,'2020/05/18') AS 'week as part of Date';

Output: