DATEPART
Syntax
DATEPART( datetime, unit [ , firstday ] )
Description
Returns a part of the date as an integer.
Depending on the argument, unit returns the following values:
"year"— the year number (see YEAR);"quarter"— the number of the quarter (from1to4) of the year (see QUARTER);"month"— the number of the month in the year (see MONTH);"week"— the number of the week in the year according to ISO 8601 (see WEEK);"dayofweek","dow"— the number of the day of the week (see DAYOFWEEK);"day"— the number of the day in the month (see DAY);"hour"— the number of the hour in the day (see HOUR);"minute"— the number of the minute in the hour (see MINUTE);"second"— the number of the second in the minute (see SECOND).
If you select "dayofweek", you can use the additional parameter firstday to specify the first day of the week (Monday by default). Learn more about this parameter in the DAYOFWEEK function description.
Argument types:
datetime—Date | Datetimeunit—Stringfirstday—String
Return type: Integer
Note
Only constant values are accepted for the arguments (firstday).
Examples
Example with date
Formulas:
- Date:
[Date]; - Year:
DATEPART([Date], "year"); - Month:
DATEPART([Date], "month"); - Day:
DATEPART([Date], "day"); - DayOfWeek:
DATEPART([Date], "dayofweek"); - DOW:
DATEPART([Date], "dow").
| Date | Year | Month | Day | DayOfWeek | DOW |
|---|---|---|---|---|---|
2014-10-06 |
2014 |
10 |
6 |
1 |
1 |
2014-10-07 |
2014 |
10 |
7 |
2 |
2 |
2017-03-08 |
2017 |
3 |
8 |
3 |
3 |
2024-02-12 |
2024 |
2 |
12 |
1 |
1 |
Example with custom first day of the week
Formulas:
- Date:
[Date]; - DOW:
DATEPART([Date], "dow"); - DOW sun:
DATEPART([Date], "dow", "sun"); - DOW Monday:
DATEPART([Date], "dow", "Monday"); - DOW wed:
DATEPART([Date], "dow", "wed").
| Date | DOW | DOW sun | DOW Monday | DOW wed |
|---|---|---|---|---|
2014-10-06 |
1 |
2 |
1 |
6 |
2014-10-07 |
2 |
3 |
2 |
7 |
2017-03-08 |
3 |
4 |
3 |
1 |
2024-02-12 |
1 |
2 |
1 |
6 |
Example with date and time
Formulas:
- DateTime:
[DateTime]; - Year:
DATEPART([DateTime], "year"); - Month:
DATEPART([DateTime], "month"); - Day:
DATEPART([DateTime], "day"); - Hour:
DATEPART([DateTime], "hour"); - Minute:
DATEPART([DateTime], "minute"); - Second:
DATEPART([DateTime], "second").
| DateTime | Year | Month | Day | Hour | Minute | Second |
|---|---|---|---|---|---|---|
2014-10-06T07:45:12 |
2014 |
10 |
6 |
7 |
45 |
12 |
2014-10-07T11:10:15 |
2014 |
10 |
7 |
11 |
10 |
15 |
2017-03-08T23:59:59 |
2017 |
3 |
8 |
23 |
59 |
59 |
2024-02-12T07:40:33 |
2024 |
2 |
12 |
7 |
40 |
33 |
Data source support
ClickHouse 21.8, Microsoft SQL Server 2017 (14.0), MySQL 5.7, Oracle Database 12c (12.1), PostgreSQL 9.3, YDB.