根据工作日或周末在SQL Server中填充一列，具体取决于日期时间 [英] Populate a column in SQL Server by weekday or weekend depending on the datetime

问题描述

我正在使用SQL Server，无法更新任何现有列，也不能向数据中添加任何其他数据/列.

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

我有一列dttime (yyyy-mm-dd hh:mm:ss)，其中包含每个条目的日期时间值. 我想选择现有的列，并选择一个新列作为wd，当一周中的某天是星期六或星期日时，它将填充为"weekend"，其他日子为工作日"

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

我正在使用以下查询:

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.

推荐答案

您可以使用

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

还可以通过 SET DATEFIRST ，因此基于DATEFIRST设置值，星期日和星期六的整数值可以更改.因此，将DATENAME替换为dttime不会引起任何混乱.

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.

您可以执行以下查询以更详细地了解，DATENAME将始终返回当前日期名称，而DATEPART将基于DATEFIRST返回.

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';

如果SELECT @@DATEFIRST返回7，则可以使用

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

在周日的1和在周六的7.

这篇关于根据工作日或周末在SQL Server中填充一列，具体取决于日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！