CODEWITHSUNDEEP
sql-serverselectcase
adventurer
adventurer

Reputation: 97

Populate a column in SQL Server by weekday or weekend depending on the datetime

I am working on SQL Server, wherein I cannot update any existing column nor can I add any more data/ column to the data.

I have a column of dttime (yyyy-mm-dd hh:mm:ss) which consists of the date time value against each entry. I want to select the existing columns , and a new column as wd which will be populated as 'weekend' when the day of the week is either Saturday or Sunday and 'weekday' in case of other days

I am using the below query:

SELECT id, total,
case when dayofweek(dttime) in (1,7) then 'Weekend' else 'Weekday' end as wd 
from table_name

Please suggest where is the mistake here, or is there any alternative to this, in case I have more than two different things to populate in a similar way, based on another column.

Upvotes: 0

Views: 4205

Answers (3)

Arulkumar
Arulkumar

Reputation: 13237

You can use DATENAME of dw or weekday to achieve your expectation:

SELECT id, total, 
       CASE WHEN DATENAME (dw, dttime) IN ('Saturday', 'Sunday') THEN 'Weekend' 
       ELSE 'Weekday' END AS wd
FROM table_name

Also your first day of the week can be set by the SET DATEFIRST, so based on the DATEFIRST set value the integer value for Sunday and Saturday can change. So getting DATENAME for the dttime will not cause any confusion.

You can execute the below query to understand more in detail, DATENAME will always return the current day name, but DATEPART will return based on the DATEFIRST. 

SELECT @@DATEFIRST AS 'First Day', 
       DATEPART(dw, GETDATE()) AS 'Today', 
       DATENAME(dw, GETDATE()) AS 'TodayName';

If SELECT @@DATEFIRST returns 7, then you can use 

CASE WHEN DATEPART(dw, dttime) IN (1, 7) THEN 'Weekend' ELSE 'Weekday' END AS wd

Here 1 for Sunday and 7 for Saturday.

Upvotes: 1

M. Ewees
M. Ewees

Reputation: 41

Please try below query

SELECT id, total,
case when DATEPART(dw,dttime) in (1,7) then 'Weekend' else 'Weekday' end as wd 
from table_name

Upvotes: 0

Prabhat G
Prabhat G

Reputation: 3029

You need DATEPART function. try this : 

SELECT id, total,
case when DATEPART(dw,dttime) in (6,7) then 'Weekend' else 'Weekday' end as wd 
from table_name

Upvotes: 0

Related Questions