Day Function

DAY (date), MONTH (date) and YEAR (date):

Returns an integer representing the day (day of the month),month and year of the specified date.

Syntax:

DAY (date)

MONTH (date)

YEAR (date)

Arguments 

Date

Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value.

The date argument can be an

expression,

column expression,

user-defined variable or

string literal.

Return Type
int
Return Value
DAY returns the same value as   DATEPART (Day, date).
Month returns the same value as DATEPART (Month, date).
YEAR returns the same value as  DATEPART (year, date).

If date contains only a time part,

The return value is 1 for DAY() function, the base day.

The return value is 1 for MONTH() function, the base day.

The return value is 1900 for YEAR() function, the base day.

Eg:
The following statement returns 1900, 1, 1. The argument for date is the number 0. SQL Server interprets 0 as January 1, 1900.
SELECT YEAR(0), MONTH(0), DAY(0);

·        dbcc useroptions

language   us_english
dateformat mdy
datefirst  7

·         declare

@var datetime

set @var='2013-01-12'

select DAY(@var)

Output:12

·         declare

@var datetime

set @var='2013-01-12'

   select month(@var)

Output:01

·         declare

@var datetime

set @var='2013-01-12'

select year(@var)

Output:2013

The sql server interpret the Numeric date in the order of SET DATEFORMAT
The four digits treated as year---> year=2013
Rest of the part interpreted in order of mdy hence 01 is Month and 12 is Day.

The SET DATEFORMAT session setting is applied while caluclating day ,month and year of specified date.

Here caluclating day,month and year of an “2010 january 12”. Initialise the date variable value as '10-01-12',Specified year in two digit form as 10(2010).

·         declare

@var datetime

set @var='10-01-12'

select day(@var) as day

select month(@var) as month

select year(@var) as year

Output:

day         month       year

----------- ----------- -----

01           10        2012

Sql Server interpreted value in SET DATEFORMAT order (mdy),it displays date as 2012 november 01.

Note:If we specify year as four digit format then server depends on SET DATEFORMAT for date and month intreprtation.

If we specify year as two digit format then server depends on SET DATEFORMAT for date,month and year intreprtation.

set dateformat mdy

declare

@var datetime

set @var='10-02-10'

select day(@var) as day

select month(@var) as month

select year(@var)  as year

Output:

day         month       year

----------- ----------- -----

2           10        2010

set dateformat dmy

declare

@var datetime

set @var='10-02-10'

select day(@var) as day

,month(@var) as month

,year(@var)  as year

Output:

day         month       year

----------- ----------- ------

10          2           2010

Hence To avoid ambiguity, use four-digit years.

DATEPART AND DATENAME:

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

Syntax:

DATEPART ( datepart , date )

DATENAME ( datepart, date )

Datepart:

Is the part of date (a date or time value) for which an integer will be returned.

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.

Return Type for DATEPART
Int
Return Type for DATENAME
nvarchar
YEAR,DAY,MONTH:
These are same as values returned by the functions DAY(),YEAR() and MONTH()

week and weekday datepart Arguments 

microsecond

nanosecond

TZoffset

ISO_WEEK

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 2012, DATEPART implicitly casts string literals as a datetime2 type. This means that DATEPART does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.
Example:
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