SQL SERVER DATEPART

SQL SERVER DATEPART 

  DATEPART function used to get Portion of the Date/time/datetime/datetimeoffset column/variable.

Portion  can be either year,month ...seconds,milli,microseconds etc.,

datepart	Abbreviations	Usage	Output
year	yy, yyyy	select datepart(yyyy,getdate()), datepart(yy,getdate()), datepart(yyyy,getutcdate()), datepart(yy,getutcdate())	2013,2013,2013,2013
quarter	qq, q	select datepart(qq,getdate()), datepart(q,getdate()), datepart(qq,getutcdate()), datepart(q,getutcdate())	1,1,1,1
month	mm, m	select datepart(month,getdate()), datepart(mm,getdate()), datepart(m,getdate()), datepart(mm,getutcdate()), datepart(m,getutcdate())	2,2,2,2,2
dayofyear	dy, y	select datepart(dayofyear,getdate()), datepart(dy,getdate()), datepart(y,getdate()), datepart(dy,getutcdate()), datepart(y,getutcdate())	50,50,50,49,49
day	dd, d	select datepart(day,getdate()), datepart(dd,getdate()), datepart(d,getdate()), datepart(dd,getutcdate()), datepart(d,getutcdate())	19,19,19,18,18
week	wk, ww	select datepart(week,getdate()), datepart(wk,getdate()), datepart(ww,getdate()), datepart(wk,getutcdate()), datepart(ww,getutcdate())	8,8,8,8,8
weekday	dw	select datepart(weekday,getdate()), datepart(dw,getdate()), datepart(weekday,getutcdate()), datepart(dw,getutcdate())	3,3,2,2
hour	hh	select datepart(hour,getdate()), datepart(hh,getdate()), datepart(hour,getutcdate()), datepart(hh,getutcdate())	1,1,20,20
minute	mi, n	select datepart(minute,getdate()), datepart(mi,getdate()), datepart(n,getdate()), datepart(mi,getutcdate()), datepart(n,getutcdate())	41,41,41,11,11
second	ss, s	select datepart(second,getdate()), datepart(ss,getdate()), datepart(s,getdate()), datepart(ss,getutcdate()), datepart(s,getutcdate())	34,34,34,34,34
millisecond	ms	select datepart(millisecond,getdate()), datepart(ms,getdate()), datepart(millisecond,getutcdate()), datepart(ms,getutcdate())	630,630,627,627
microsecond	mcs	select datepart(microsecond,getdate()), datepart(mcs,getdate()), datepart(microsecond,getutcdate()), datepart(mcs,getutcdate())	747000,747000,747000,747000
nanosecond	ns	select datepart(nanosecond,getdate()), datepart(ns,getdate()), datepart(nanosecond,getutcdate()), datepart(ns,getutcdate())	640000000,640000000,637000000,637000000
TZoffset	tz	select datepart(TZoffset,getdate()), datepart(tz,getdate()), datepart(TZoffset,getutcdate()), datepart(tz,getutcdate())	Msg 9810, Level 16, State 6, Line 1 The datepart tzoffset is not supported by date function datepart for data type unknown.
ISO_WEEK	isowk, isoww	select datepart(ISO_WEEK,getdate()), datepart(isowk,getdate()), datepart(isoww,getdate()), datepart(isowk,getutcdate()), datepart(isoww,getutcdate())	8,8,8,8,8

datepart	Abbreviations	Usage	Output
year	yy, yyyy	select datepart(yyyy,getdate()), datepart(yy,getdate()), datepart(yyyy,getutcdate()), datepart(yy,getutcdate())	2013,2013,2013,2013
quarter	qq, q	select datepart(qq,getdate()), datepart(q,getdate()), datepart(qq,getutcdate()), datepart(q,getutcdate())	1,1,1,1
month	mm, m	select datepart(month,getdate()), datepart(mm,getdate()), datepart(m,getdate()), datepart(mm,getutcdate()), datepart(m,getutcdate())	2,2,2,2,2
dayofyear	dy, y	select datepart(dayofyear,getdate()), datepart(dy,getdate()), datepart(y,getdate()), datepart(dy,getutcdate()), datepart(y,getutcdate())	50,50,50,49,49
day	dd, d	select datepart(day,getdate()), datepart(dd,getdate()), datepart(d,getdate()), datepart(dd,getutcdate()), datepart(d,getutcdate())	19,19,19,18,18
week	wk, ww	select datepart(week,getdate()), datepart(wk,getdate()), datepart(ww,getdate()), datepart(wk,getutcdate()), datepart(ww,getutcdate())	8,8,8,8,8
weekday	dw	select datepart(weekday,getdate()), datepart(dw,getdate()), datepart(weekday,getutcdate()), datepart(dw,getutcdate())	3,3,2,2
hour	hh	select datepart(hour,getdate()), datepart(hh,getdate()), datepart(hour,getutcdate()), datepart(hh,getutcdate())	1,1,20,20
minute	mi, n	select datepart(minute,getdate()), datepart(mi,getdate()), datepart(n,getdate()), datepart(mi,getutcdate()), datepart(n,getutcdate())	41,41,41,11,11
second	ss, s	select datepart(second,getdate()), datepart(ss,getdate()), datepart(s,getdate()), datepart(ss,getutcdate()), datepart(s,getutcdate())	34,34,34,34,34
millisecond	ms	select datepart(millisecond,getdate()), datepart(ms,getdate()), datepart(millisecond,getutcdate()), datepart(ms,getutcdate())	630,630,627,627
microsecond	mcs	select datepart(microsecond,getdate()), datepart(mcs,getdate()), datepart(microsecond,getutcdate()), datepart(mcs,getutcdate())	747000,747000,747000,747000
nanosecond	ns	select datepart(nanosecond,getdate()), datepart(ns,getdate()), datepart(nanosecond,getutcdate()), datepart(ns,getutcdate())	640000000,640000000,637000000,637000000
TZoffset	tz	select datepart(TZoffset,getdate()), datepart(tz,getdate()), datepart(TZoffset,getutcdate()), datepart(tz,getutcdate())	Msg 9810, Level 16, State 6, Line 1 The datepart tzoffset is not supported by date function datepart for data type unknown.
ISO_WEEK	isowk, isoww	select datepart(ISO_WEEK,getdate()), datepart(isowk,getdate()), datepart(isoww,getdate()), datepart(isowk,getutcdate()), datepart(isoww,getutcdate())	8,8,8,8,8

Ex:Get Time only  from datetime column/GetDate()

select concat(datepart(hh,getdate()),':',datepart(minute,getdate()),':',datepart(ss,getdate()))
O/P:
time
1:58:46

Tags:SQL SERVER DATEPART ,DatePart year,
DatePart month,DatePart dayofweek,
DatePart dayofyear,DatePart day,DatePart hour,DatePart minute,
DatePart second,
DatePart microsecond,DatePart millisecond,
DatePart  datetime offset,
DatePart iso week,
DatePart quarter