DATENAME() Function in SQL server:
The DATENAME() function returns a specified part of a
date. It returns nvarchar as a result. Same function can be used in SQL
Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse,
Parallel Data Warehouse.
Syntax
DATENAME
( datepart , date )
|
Arguments or Parameters: This function has two
parameters.
1. Datepart
: datepart is specific part that will return by this function. Following is the
list of all valid datepart arguments.
datepart
|
Abbreviations
|
year
|
yy, yyyy
|
quarter
|
qq, q
|
month
|
mm, m
|
dayofyear
|
dy, y
|
day
|
dd, d
|
week
|
wk, ww
|
weekday
|
dw, w
|
hour
|
hh
|
minute
|
mi, n
|
second
|
ss, s
|
millisecond
|
ms
|
microsecond
|
mcs
|
nanosecond
|
ns
|
TZoffset
|
tz
|
ISO_WEEK
|
ISOWK, ISOWW
|
2.
date : Second parameter is date. From this date Datename() function
will get specific part mentioned in first parameter.
Date can be an expression that can
resolve to one of the following data types:
a.
date
b.
datetime
c.
datetimeoffset
d.
datetime2
e.
smalldatetime
f.
time
As date, Datename() will
accept a column expression, expression, string literal, or user-defined
variable. We must use four-digit years to avoid ambiguity issues.
Return Type: nvarchar
Note: Datepart & Abbreviation both give same result.
Example:
Return year from date:
SELECT DATENAME(yy, '2020-03-02') AS DatePartResult;
Result:
2020
Return month from date:
SELECT DATENAME(MONTH,
'2020-03-02') AS DatePartResult;
Result:
March
Return hour from date:
SELECT DATENAME(HOUR,
'2020-03-02 08:36')
AS DatePartResult;
Result:
8
Return minute from date:
SELECT DATENAME(MINUTE,
'2020-03-02 08:36')
AS DatePartResult;
Result:
36
Return week from date:
SELECT DATENAME(WEEK,
'2020-03-02 08:36')
AS DatePartResult;
Result:
10
|