SQL 查询帮助：以非平凡的方式转换日期

发布于 2024-07-04 19:18:32 字数 473 浏览 7 评论 0原文

我有一个带有“日期”列的表，我想做一个执行以下操作的查询：

如果日期是星期一、星期二、星期三或星期四，显示的日期应向上移动 1 天，如

DATEADD(day, 1, [Date])

On the other hand, if it is a Friday, the displayed date should be incremented by 3 days (i.e. so it becomes the following Monday).

如何在 SELECT 语句中执行此操作？如

SELECT somewayofdoingthis([Date]) FROM myTable

（这是 SQL Server 2000。）

原文

I have a table with a "Date" column, and I would like to do a query that does the following:

If the date is a Monday, Tuesday, Wednesday, or Thursday, the displayed date should be shifted up by 1 day, as in

DATEADD(day, 1, [Date])

On the other hand, if it is a Friday, the displayed date should be incremented by 3 days (i.e. so it becomes the following Monday).

How do I do this in my SELECT statement? As in,

SELECT somewayofdoingthis([Date]) FROM myTable

(This is SQL Server 2000.)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小ぇ时光︴ 2024-07-11 19:18:33

从数据仓库人员那里获取页面并制作一个表格怎么样？在 DW 术语中，这将是一个日期维度。标准日期维度会有各种日期名称（“MON”、“Monday”）、“1998 年 8 月 22 日”），或月末和月初等指标。但是，您也可以拥有仅在您的环境中有意义的列。

例如，根据问题，您的下一个工作日列可能会指向相关日期的关键。这样您就可以进一步自定义它以考虑假期或其他非工作日。

DW 人员坚持使用无意义的密钥（即，不要只使用截断的日期作为密钥，而是使用生成的密钥），但您可以自己决定。

日期维度工具包具有在各种 DBMS 中生成您自己的表的代码，并且它具有多个 CSV 数据多年的约会。

回复收藏 0 原文

污味仙女 2024-07-11 19:18:33

您需要创建一个 SQL 函数来为您执行此转换。

回复收藏 0 原文

司马昭之心 2024-07-11 19:18:33

这与 Brian 的大部分类似，只是由于括号不匹配而无法编译，并且我将 IF 更改为不包含选择。需要注意的是，我们在这里使用 DateNAME 而不是 datePART，因为 datePART 取决于 SET DATEFIRST 设置的值，该值设置一周的第一天。

CREATE FUNCTION dbo.GetNextWDay(@Day datetime)
RETURNS DATETIME
AS
BEGIN
    DECLARE @ReturnDate DateTime

    set @ReturnDate = dateadd(dd, 1, @Day)
    if datename(dw, @ReturnDate) = 'Saturday'
        set @ReturnDate = dateadd(dd, 2, @ReturnDate)
    if datename(dw, @ReturnDate) = 'Sunday'
        set @ReturnDate = dateadd(dd, 1, @ReturnDate)
    RETURN @ReturnDate
END

This is mostly like Brian's except it didn't compile due to mismatched parens and I changed the IF to not have the select in it. It is important to note that we use DateNAME here rather than datePART because datePART is dependent on the value set by SET DATEFIRST, which sets the first day of the week.

CREATE FUNCTION dbo.GetNextWDay(@Day datetime)
RETURNS DATETIME
AS
BEGIN
    DECLARE @ReturnDate DateTime

    set @ReturnDate = dateadd(dd, 1, @Day)
    if datename(dw, @ReturnDate) = 'Saturday'
        set @ReturnDate = dateadd(dd, 2, @ReturnDate)
    if datename(dw, @ReturnDate) = 'Sunday'
        set @ReturnDate = dateadd(dd, 1, @ReturnDate)
    RETURN @ReturnDate
END

回复收藏 0 原文

耳根太软 2024-07-11 19:18:33

create table #dates (dt datetime)
insert into #dates (dt) values ('1/1/2001')
insert into #dates (dt) values ('1/2/2001')
insert into #dates (dt) values ('1/3/2001')
insert into #dates (dt) values ('1/4/2001')
insert into #dates (dt) values ('1/5/2001')

    select
        dt, day(dt), dateadd(dd,1,dt)
    from
        #dates
    where
        day(dt) between 1 and 4

    union all

    select
        dt, day(dt), dateadd(dd,3,dt)
    from
        #dates
    where
        day(dt) = 5

    drop table #dates

create table #dates (dt datetime)
insert into #dates (dt) values ('1/1/2001')
insert into #dates (dt) values ('1/2/2001')
insert into #dates (dt) values ('1/3/2001')
insert into #dates (dt) values ('1/4/2001')
insert into #dates (dt) values ('1/5/2001')

    select
        dt, day(dt), dateadd(dd,1,dt)
    from
        #dates
    where
        day(dt) between 1 and 4

    union all

    select
        dt, day(dt), dateadd(dd,3,dt)
    from
        #dates
    where
        day(dt) = 5

    drop table #dates

回复收藏 0 原文

赠意 2024-07-11 19:18:33

这是我的想法，可以清楚地清理，但可以将其作为起点：

select case when DATENAME(dw, [date]) = 'Monday' then DATEADD(dw, 1, [Date])
                when DATENAME(dw, [date]) = 'Tuesday' then DATEADD(dw, 1, [Date])
                when DATENAME(dw, [date]) = 'Wednesday' then DATEADD(dw, 1, [Date])
                when DATENAME(dw, [date]) = 'Thursday' then DATEADD(dw, 1, [Date])
                when  DATENAME(dw, [date]) = 'Friday' then DATEADD(dw, 3, [Date])
          end as nextDay
    ...

This is off the top of my head and can be clearly cleaned up but use it as a starting point:

select case when DATENAME(dw, [date]) = 'Monday' then DATEADD(dw, 1, [Date])
                when DATENAME(dw, [date]) = 'Tuesday' then DATEADD(dw, 1, [Date])
                when DATENAME(dw, [date]) = 'Wednesday' then DATEADD(dw, 1, [Date])
                when DATENAME(dw, [date]) = 'Thursday' then DATEADD(dw, 1, [Date])
                when  DATENAME(dw, [date]) = 'Friday' then DATEADD(dw, 3, [Date])
          end as nextDay
    ...

回复收藏 0 原文

美羊羊 2024-07-11 19:18:33

查找 CASE 语句和 DATEPART 语句。您将需要将 dw 参数与 DATEPART 一起使用来获取表示星期几的整数。

回复收藏 0 原文

夜深人未静 2024-07-11 19:18:33

你可以用这个：

select dayname,newdayname =
    CASE dayname
    WHEN 'Monday' THEN 'Tuesday'
    WHEN 'Tuesday' THEN 'Wednesday'
    WHEN 'Wednesday' THEN 'Thursday'
    WHEN 'Thursday' THEN 'Friday'
    WHEN 'Friday' THEN 'Monday'
    WHEN 'Saturday' THEN 'Monday'
    WHEN 'Sunday' THEN 'Monday'
END
FROM UDO_DAYS

results:
Monday       Tuesday
Tuesday      Wednesday
Wednesday    Thursday
Thursday     Friday
Friday       Monday
Saturday     Monday
Sunday       Monday

table data:
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

you could use this:

select dayname,newdayname =
    CASE dayname
    WHEN 'Monday' THEN 'Tuesday'
    WHEN 'Tuesday' THEN 'Wednesday'
    WHEN 'Wednesday' THEN 'Thursday'
    WHEN 'Thursday' THEN 'Friday'
    WHEN 'Friday' THEN 'Monday'
    WHEN 'Saturday' THEN 'Monday'
    WHEN 'Sunday' THEN 'Monday'
END
FROM UDO_DAYS

results:
Monday       Tuesday
Tuesday      Wednesday
Wednesday    Thursday
Thursday     Friday
Friday       Monday
Saturday     Monday
Sunday       Monday

table data:
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

回复收藏 0 原文

看透却不说透 2024-07-11 19:18:33

听起来像 CASE 表达式。我不知道 SQL Server 的正确数据操作，但基本上它看起来像这样：

CASE
  WHEN [Date] is a Friday THEN DATEADD( day, 3, [Date] )
  ELSE DATEADD( day, 1, [Date] )
END

如果您想检查周末，您可以在 ELSE 之前添加额外的 WHEN 子句。

Sounds like a CASE expression. I don't know the proper data manipulations for SQL Server, but basically it would look like this:

CASE
  WHEN [Date] is a Friday THEN DATEADD( day, 3, [Date] )
  ELSE DATEADD( day, 1, [Date] )
END

If you wanted to check for weekend days you could add additional WHEN clauses before the ELSE.

回复收藏 0 原文

岁月如刀 2024-07-11 19:18:32

我假设您还希望将周六和周日提前到下周一。如果不是这种情况，请从 (1,2,3,4,5) 中取出 1，并删除最后一个 when 子句。

case
   --Sunday thru Thursday are shifted forward 1 day
   when datepart(weekday, [Date]) in (1,2,3,4,5) then dateadd(day, 1, [Date]) 
   --Friday is shifted forward to Monday
   when datepart(weekday, [Date]) = 6  then dateadd(day, 3, [Date])
   --Saturday is shifted forward to Monday
   when datepart(weekday, [Date]) = 7  then dateadd(day, 2, [Date])
end

您也可以在一行中完成：

select dateadd(day, 1 + (datepart(weekday, [Date])/6) * (8-datepart(weekday, [Date])), [Date])

I'm assuming that you also want Saturday and Sunday to shift forward to the following Monday. If that is not the case, take the 1 out of (1,2,3,4,5) and remove the last when clause.

case
   --Sunday thru Thursday are shifted forward 1 day
   when datepart(weekday, [Date]) in (1,2,3,4,5) then dateadd(day, 1, [Date]) 
   --Friday is shifted forward to Monday
   when datepart(weekday, [Date]) = 6  then dateadd(day, 3, [Date])
   --Saturday is shifted forward to Monday
   when datepart(weekday, [Date]) = 7  then dateadd(day, 2, [Date])
end

You can also do it in one line:

select dateadd(day, 1 + (datepart(weekday, [Date])/6) * (8-datepart(weekday, [Date])), [Date])

回复收藏 0 原文

丿*梦醉红颜 2024-07-11 19:18:32

尝试

select case  when datepart(dw,[Date]) between 2 and 5 then DATEADD(dd, 1, [Date])
when datepart(dw,[Date]) = 6 then DATEADD(dd, 3, [Date]) else [Date] end as [Date]

Try

select case  when datepart(dw,[Date]) between 2 and 5 then DATEADD(dd, 1, [Date])
when datepart(dw,[Date]) = 6 then DATEADD(dd, 3, [Date]) else [Date] end as [Date]

回复收藏 0 原文

樱＆纷飞 2024-07-11 19:18:32

CREATE FUNCTION dbo.GetNextWDay(@Day datetime)
RETURNS DATETIME
AS
BEGIN 
    DECLARE @ReturnDate DateTime

    set @ReturnDate = dateadd(dd, 1, @Day)

    if (select datename(@ReturnDate))) = 'Saturday'
        set @ReturnDate = dateadd(dd, 2, @ReturnDate)

    if (select datename(@ReturnDate) = 'Sunday'
        set @ReturnDate = dateadd(dd, 1, @ReturnDate)

    RETURN @ReturnDate
END

CREATE FUNCTION dbo.GetNextWDay(@Day datetime)
RETURNS DATETIME
AS
BEGIN 
    DECLARE @ReturnDate DateTime

    set @ReturnDate = dateadd(dd, 1, @Day)

    if (select datename(@ReturnDate))) = 'Saturday'
        set @ReturnDate = dateadd(dd, 2, @ReturnDate)

    if (select datename(@ReturnDate) = 'Sunday'
        set @ReturnDate = dateadd(dd, 1, @ReturnDate)

    RETURN @ReturnDate
END

回复收藏 0 原文

没︽人懂的悲伤 2024-07-11 19:18:32

我将这样做。如果您将在其他地方使用它，我确实推荐像上面这样的功能。

CASE
WHEN
    DATEPART(dw, [Date]) IN (2,3,4,5)
THEN
    DATEADD(d, 1, [Date])
WHEN
    DATEPART(dw, [Date]) = 6
THEN
    DATEADD(d, 3, [Date])
ELSE
    [Date]
END AS [ConvertedDate]

Here is how I would do it. I do recommend a function like above if you will be using this in other places.

CASE
WHEN
    DATEPART(dw, [Date]) IN (2,3,4,5)
THEN
    DATEADD(d, 1, [Date])
WHEN
    DATEPART(dw, [Date]) = 6
THEN
    DATEADD(d, 3, [Date])
ELSE
    [Date]
END AS [ConvertedDate]

回复收藏 0 原文

~没有更多了~