DATEPART and DATENAME Functions in SQL SERVER

DATE Functions in SQL SERVER :

DATEPART(): datepart() is a SQL Server function that extracts a specific part of the date/time value. Its syntax is as follows:

DATEPART (part_of_day, expression)
 
examples: 

1.SELECT DATEPART(month, GETDATE()) AS 'Month Number'

2.SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0) 

DATENAME(): datename()  function returns a string value. With the DATENAME function, the only units of time that return values different than the DATEPART function are the WEEKDAY and MONTH.
Unit of time Abbreviations Query Result

DATEDIFF(): DATEDIFF function is used to calculate the difference between two days, and is used in MySQL and SQL Server. The syntax for this date function is different between these two databases, so each one is discussed below:
MySQL:
The usage for the DATEDIFF function 
DATEDIFF (expression1, expression2

example: SELECT DATEDIFF('2000-01-10','2000-01-05');

GETDATE(): this function is used to retrieve the current database system time in SQL Server. Its syntax is
GETDATE()
GETDATE does not require any argument.
Example: The SQL statement
SELECT GETDATE();
yields the following result:
2011-10-22 11:27:10.247
GETDATE function is most useful when we need to record the time a particular transaction happens. In SQL Server, we simply insert the value of the GETDATE() function into the table to achieve this. We can also set the default value of a column to be GETDATE() to achieve the same purpose.

DATEPART (The @Date value used is '2011-09-25 19:47:00.8631597')
Unit of time	Abbreviations	Query	Result
ISO_WEEK	isowk, isoww	SELECT DATEPART(ISO_WEEK,@Date)	38
TZoffset	tz	SELECT DATEPART(TZoffset,@Date)	0
NANOSECOND	ns	SELECT DATEPART(NANOSECOND,@Date)	863159700
MICROSECOND	mcs	SELECT DATEPART(MICROSECOND,@Date)	863159
MILLISECOND	ms	SELECT DATEPART(MS,@Date)	863
SECOND	ss, s	SELECT DATEPART(SS,@Date)	0
MINUTE	mi, n	SELECT DATEPART(MINUTE,@Date)	47
HOUR	hh	SELECT DATEPART(HH,@Date)	19
WEEKDAY	dw	SELECT DATEPART(DW,@Date)	1
WEEK	wk, ww	SELECT DATEPART(WEEK,@Date)	40
DAY	dd, d	SELECT DATEPART(DAY,@Date)	25
DAYOFYEAR	dy, y	SELECT DATEPART(DAYOFYEAR,@Date)	268
MONTH	mm, m	SELECT DATEPART(MM,@Date)	9
QUARTER	qq, q	SELECT DATEPART(QUARTER,@Date)	3
YEAR	yy, yyyy	SELECT DATEPART(YYYY,@Date)	2011
DATENAME (The @Date value used is '2011-09-25 19:47:00.8631597')
Unit of time	Abbreviations	Query	Result
WEEKDAY	dw	SELECT DATENAME(WEEKDAY,@Date)	Sunday
MONTH	mm, m	SELECT DATENAME(MM,@Date)	September