The SQL function that you could use is datename(datepart, date). Datepart is kind of properties that you’d like to use. For example, dw is kind of datepart to define week day, or mm to define month name. Here is the example
select datename(dw, getdate())
The result is
Thursday
You could try others datepart such as dw, mm, dy, wk, ww. Have a nice try 
