DATEPART

    Date/Time functions

    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).
    • "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: Number (whole)

    Note

    Only constant values are accepted for arguments (firstday).

    Examples

    DATEPART(#2019-01-23#, "year") = 2019
    
    DATEPART(#2019-01-23#, "month") = 1
    
    DATEPART(#2019-01-23#, "day") = 23
    
    DATEPART(#2019-01-23 11:47:07#, "hour") = 11
    
    DATEPART(#2019-01-23 11:47:07#, "minute") = 47
    
    DATEPART(#2019-01-23 11:47:07#, "second") = 7
    
    DATEPART(#1971-01-14 01:02:03#, "dayofweek") = 4
    
    DATEPART(#1971-01-14 01:02:03#, "dayofweek", "wed") = 2
    

    Data source support

    Materialized Dataset, ClickHouse 1.1, Microsoft SQL Server 2017 (14.0), MySQL 5.6, PostgreSQL 9.3