Select day(monday) in SQL Server

I have a field with dd-mm-yy hh:mm:ss tt

I want to select only the name of the day

I tried datepart() and day()

both开发者_C百科 gave me 1 to 31.

What I want is Monday, Tuesday and so on..

How to achieve that?

SELECT DATEPART(WEEKDAY, GETDATE())

or

SELECT DATENAME(WEEKDAY, GETDATE())

SQL doesn' have a direct conversion to weekday names but you can workaround like this

SET DATEFIRST 1 -- Start with Monday

SELECT SUBSTRING('MonTueWedThuFriSatSun', 1 + (DATEPART(weekday,[myfield]) - 1) * 3)

or if you want the long names

SELECT SUBSTRING('Monday Tuesday WednesdayThursday Friday Saturday Sunday ', 1 + (DATEPART(weekday,[myfield]) - 1) * 9)

select to_char(col_name, 'DAY') from table_name;