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 (from 1 to 4) 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:

    • datetimeDate | Datetime
    • unitString
    • firstdayString

    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 19.13, Microsoft SQL Server 2017 (14.0), MySQL 5.6, PostgreSQL 9.3.