مشاركة عبر

  • مقالة

DATEPART (Transact-SQL)

Returns an integer that represents the specified datepart of the specified date.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL). For information and examples that are common to date and time data types and functions, see Using Date and Time Data.

Topic link iconTransact-SQL Syntax Conventions

Syntax

DATEPART (datepart , date )

Arguments

  • datepart
    Is the part of date (a date or time value) for which an integer will be returned. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

    datepart

    Abbreviations

    year

    yy, yyyy

    quarter

    qq, q

    month

    mm, m

    dayofyear

    dy, y

    day

    dd, d

    week

    wk, ww

    weekday

    dw

    hour

    hh

    minute

    mi, n

    second

    ss, s

    millisecond

    ms

    microsecond

    mcs

    nanosecond

    ns

    TZoffset

    tz

    ISO_WEEK

    isowk, isoww

  • date
    Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable, or string literal.

    To avoid ambiguity, use four-digit years. For information about two digits years, see two digit year cutoff Option.

Return Type

int

Return Value

Each datepart and its abbreviations return the same value.

The return value depends on the language environment set by using SET LANGUAGE and by the default language of the login. If date is a string literal for some formats, the return value depends on the format specified by using SET DATEFORMAT. SET DATEFORMAT does not affect the return value when the date is a column expression of a date or time data type.

The following table lists all datepart arguments with corresponding return values for the statement SELECT DATEPART(datepart,'2007-10-30 12:15:32.1234567 +05:10'). The data type of the date argument is datetimeoffset(7). The nanoseconddatepart return value has a scale of 9 (.123456700) and the last two positions are always 00.

datepart

Return value

year, yyyy, yy

2007

quarter, qq, q

4

month, mm, m

10

dayofyear, dy, y

303

day, dd, d

30

week, wk, ww

45

weekday, dw

1

hour, hh

12

minute, n

15

second, ss, s

32

millisecond, ms

123

microsecond, mcs

123456

nanosecond, ns

123456700

TZoffset, tz

310

week and weekday datepart Arguments

When datepart is week (wk, ww) or weekday (dw), the return value depends on the value that is set by using SET DATEFIRST.

January 1 of any year defines the starting number for the weekdatepart, for example: DATEPART (wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

The following table lists the return value for week and weekdaydatepart for '2007-04-21 ' for each SET DATEFIRST argument. January 1 is a Sunday in the year 2007. April 21 is a Saturday in the year 2007. SET DATEFIRST 7, Sunday, is the default for U.S. English.

SET DATEFIRST

argument

week

returned

weekday

returned

1

16

6

2

17

5

3

17

4

4

17

3

5

17

2

6

17

1

7

16

7

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, f respectively.

ISO_WEEK datepart

ISO 8601 includes the ISO week-date system, a numbering system for weeks. Each week is associated with the year in which Thursday occurs. For example, week 1 of 2004 (2004W01) ran from Monday 29 December 2003 to Sunday, 4 January 2004. The highest week number in a year might be 52 or 53. This style of numbering is typically used in European countries, but rare elsewhere.

The numbering system in different countries might not comply with the ISO standard. There are at least six possibilities as shown in the following table

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

TZoffset

The TZoffset (tz) is returned as the number of minutes (signed). The following statement returns a time zone offset of 310 minutes.

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

If the datepart argument is TZoffset (tz) and the date argument is not of datetimeoffset data type, NULL is returned.

smalldatetime date Argument

When date is smalldatetime, seconds are returned as 00.

Default Returned for a datepart That Is Not in a date Argument

If the data type of the date argument does not have the specified datepart, the default for that datepart will be returned.

For example, the default year-month-day for any date data type is 1900-01-01. The following statement has date part arguments for datepart, a time argument for date, and 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');

The default hour-minute-second for the time data type is 00:00:00. The following statement has time part arguments for datepart, a date argument for date, and returns 0, 0, 0.

SELECT DATEPART(hour, '2007-06-01')
    ,DATEPART(minute, '2007-06-01')
    ,DATEPART(second, '2007-06-01');

Fractional Seconds

Fractional seconds are returned as shown in the following statements:

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

DATEPART can be used in the select list, WHERE, HAVING, GROUP BY and ORDER BY clauses. In SQL Server 2008, DATEPART implicitly casts string literals as datetime2 types. When using DATEPART with DATEADD, avoid implicit casts of string literals. For more information, see DATEADD (Transact-SQL)

Examples

The following example returns the base year. The base year is useful for date calculations. In the example, the date is specified as a number. Notice that SQL Server interprets 0 as January 1, 1900. 

SELECT DATEPART(year, 0), DATEPART(month, 0), DATEPART(day, 0);
-- Returns: 1900    1    1 */

See Also

Reference

CAST and CONVERT (Transact-SQL)