Finding Day in T-SQL

Question

Hi, Please let me know how can I find if it is Sunday on given Date?

EDIT:

From answers another thing comes to my mind: How to find what is first day of week?

Answer 1

SELECT DATEPART(dw,theDateRow) AS dateOfWeek FROM someTable

dw stands for Day of Week

if dw = 0, it is Sunday.

Answer 2

Use the "datename" function:

print datename(weekday, '11/29/2009')

or with a variable:

declare @date smalldatetime
set @date = '12/25/2008'
select datename(weekday, @date)

Answer 3

A) Sunday Check

SELECT 
 CASE WHEN DATENAME(WEEKDAY, GETDATE()) != 'Sunday' 
  Then 'It is ' + CAST(DATENAME(WEEKDAY, GETDATE()) AS VARCHAR(10)) 
  Else  'It is ' + CAST(DATENAME(WEEKDAY, GETDATE()) AS VARCHAR(10))
 END 'Day is'

Output:

Day is
It is Saturday

B) Find first day of week

SELECT 
DayName = DATENAME(WEEKDAY,
      DATEADD(DD, 1 - DATEPART(DW, CONVERT(VARCHAR(10), GETDATE(), 111)),
      CONVERT(VARCHAR(10), GETDATE(), 111))),
Date = DATEADD(DD, 
      1 - DATEPART(DW, CONVERT(VARCHAR(10), GETDATE(), 111)),
               CONVERT(VARCHAR(10), GETDATE(), 111))

Output:

DayName Date
Sunday 2009-12-27 00:00:00.000

Answer 4

To find what is the first day of the week you use:

print @@datefirst

This will return a number from 1 to 7, where 1 = Monday, 2 = Tuesday... 7 = Sunday. You can change what is considered the first day of the week (on the SQL instance) by using:

set datefirst = 1

This will set the first day to Monday. By default SQL is set to 7 (Sunday) in US.