MS SQL - What day of week is this date?

By xngo on October 2, 2019

In MS SQL server, you can use the following two functions to get the day of the week:

  • DATEPART: It returns an integer representing the day of the week.
  • DATENAME: It returns the name representing the day of the week.
SELECT DATEPART(dw,GETDATE()) -- 5
SELECT DATENAME(dw,GETDATE()) -- Thursday

These 2 functions can return other kinds of information about a date. You simply have to change the dw abbreviation to something else. Here are the possible abbreviations.

^  Abbreviations  ^  datepart     ^
|  yy, yyyy       |  year         |
|  qq, q          |  quarter      |
|  mm, m          |  month        |
|  dy, y          |  dayofyear    |
|  dd, d          |  day          |
|  wk, ww         |  week         |
|  dw             |  weekday      |
|  hh             |  hour         |
|  mi, n          |  minute       |
|  ss, s          |  second       |
|  ms             |  millisecond  |
|  mcs            |  microsecond  |
|  ns             |  nanosecond   |
|  tz             |  TZoffset     |
|  isowk, isoww   |  ISO_WEEK     |

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.