iT邦幫忙

1

SQL的日期計算~自動推算每5天一個日期顯示

sql

這個在弄一個每次倒數計時的活動,想到的@@~

就是每5天自動算出下一個第5天的日期

例如基準日是5/21
假設日期是5/23
得到第5天的日期是5/26

但若假設日期是5/28
得到下一個第5天的日期是5/31

這個算是數學計算+SQL應用吧,看各位大大會如何解~
我是已經解好的..

--這是我初期設定
declare @basedate date = '2019/5/21'
declare @Setdate date = '2019/5/28'
declare @n int = 5

https://ithelp.ithome.com.tw/upload/images/20190521/20061369HSTPGYDI4p.png


補~我原先的SQL

declare @basedate date = '2019/5/21'
declare @Setdate date = '2019/5/28'
declare @n int = 5

select (
	case when DayNum % @n = 0
	then dateadd(d,Convert(int,DayNum / @n) * @n,@basedate)
	else dateadd(d,Convert(int,DayNum / @n) * @n + @n,@basedate)
	end
) as CheckDate
from (
	select datediff(d,@basedate,@Setdate) as DayNum
) as k

6
暐翰
iT邦大師 1 級 ‧ 2019-05-21 13:03:42
最佳解答

我直覺寫法使用datediff + mod + dateadd來解決

--這是我初期設定
declare @basedate date = '2019/5/21';
declare @Setdate date = '2019/5/28';
declare @n int = 5;
declare @addDay int = @n - (datediff(day,@basedate,@Setdate)) % ( @n  ); --藉由日期間隔 - (輸入日期與基本日期天數差 Mod 日期間隔) 得出與下一個日期天數差

select @addDay diffDay,dateadd(day,@addDay,@Setdate) as CheckDay --輸入日期加上前面的天數差得到結果

線上測試連結

image

喔喔~你的寫法~果然不一樣~哈^^~
果然邏輯精簡~
晚點再結束~

dragonH iT邦大師 3 級 ‧ 2019-05-21 14:17:52 檢舉

Database也有這種線上測試的網站唷

第一次看到(筆記/images/emoticon/emoticon07.gif

暐翰 iT邦大師 1 級 ‧ 2019-05-21 17:26:32 檢舉

純真的人 謝謝大大總是提供好玩的SQL題目
dragonH 我是在S.O那邊知道這個工具網站的 :D

0
小魚
iT邦高手 1 級 ‧ 2019-05-21 14:30:07

假設日期是5/23
得到第5天的日期是5/26

但若假設日期是5/28
得到下一個第5天的日期是5/31

這怎麼算5天的??

其實SQL本身就有函式幫你計算日期,
你不用自己處理.

dragonH iT邦大師 3 級 ‧ 2019-05-21 15:01:55 檢舉

他有說基準日5/21呀

日期在 5/21 到 5/26 => 5/26
日期在 5/26 到 5/31 => 5/31

dragonH
對唷~是自己自訂一個日期開始~

小魚 iT邦高手 1 級 ‧ 2019-05-21 16:00:29 檢舉

喔喔, 懂了, 是從5/21開始算就是了...

5
一級屠豬士
iT邦高手 1 級 ‧ 2019-05-21 15:32:02
-- 使用 Postgresql rangetype 及其運算子,函數.
-- 產生 daterange 型態 ; 含起始日,不含終止日
-- 舉第一個range
-- [2019-05-21,2019-05-26) <== 含 0521, 不含 0526 ; 
-- 從 2019-05-21 00:00:00 含此時刻 及之後
-- 2019-05-26 00:00:00 <- 此一時刻不含,之前均有包含.

with t1 as (
select n+1 as "dt_id"
     , (date '2019-05-21' + interval '5 day' * n)::date as "st_dt"
     , (date '2019-05-21' + interval '5 day' * (n+1))::date as "ed_dt"
  from generate_series(0, 5) as g(n)
), t2 as (
select dt_id
     , st_dt
     , daterange(st_dt, ed_dt, '[)') as "days_range"
  from t1
)
select *
  from t2
;

+-------+------------+-------------------------+
| dt_id |   st_dt    |       days_range        |
+-------+------------+-------------------------+
|     1 | 2019-05-21 | [2019-05-21,2019-05-26) |
|     2 | 2019-05-26 | [2019-05-26,2019-05-31) |
|     3 | 2019-05-31 | [2019-05-31,2019-06-05) |
|     4 | 2019-06-05 | [2019-06-05,2019-06-10) |
|     5 | 2019-06-10 | [2019-06-10,2019-06-15) |
|     6 | 2019-06-15 | [2019-06-15,2019-06-20) |
+-------+------------+-------------------------+
(6 rows)

-------------
-- 使用 <@ 包含於 運算子
-- 計算 date '2019-05-23' 包含於 何 daterange
with t1 as (
select n+1 as "dt_id"
     , (date '2019-05-21' + interval '5 day' * n)::date as "st_dt"
     , (date '2019-05-21' + interval '5 day' * (n+1))::date as "ed_dt"
  from generate_series(0, 5) as g(n)
), t2 as (
select dt_id
     , st_dt
     , daterange(st_dt, ed_dt, '[)') as "days_range"
  from t1
)
select *
     , date '2019-05-23' <@ days_range as "is_include"
  from t2;

+-------+------------+-------------------------+------------+
| dt_id |   st_dt    |       days_range        | is_include |
+-------+------------+-------------------------+------------+
|     1 | 2019-05-21 | [2019-05-21,2019-05-26) | t          |
|     2 | 2019-05-26 | [2019-05-26,2019-05-31) | f          |
|     3 | 2019-05-31 | [2019-05-31,2019-06-05) | f          |
|     4 | 2019-06-05 | [2019-06-05,2019-06-10) | f          |
|     5 | 2019-06-10 | [2019-06-10,2019-06-15) | f          |
|     6 | 2019-06-15 | [2019-06-15,2019-06-20) | f          |
+-------+------------+-------------------------+------------+
(6 rows)

----------
-- 使用 upper() 函數 , 找出 upper bound of range

with t1 as (
select n+1 as "dt_id"
     , (date '2019-05-21' + interval '5 day' * n)::date as "st_dt"
     , (date '2019-05-21' + interval '5 day' * (n+1))::date as "ed_dt"
  from generate_series(0, 5) as g(n)
), t2 as (
select dt_id
     , st_dt
     , daterange(st_dt, ed_dt, '[)') as "days_range"
  from t1
)
select *
     , upper(days_range) 
  from t2
 where date '2019-05-23' <@ days_range;

+-------+------------+-------------------------+------------+
| dt_id |   st_dt    |       days_range        |   upper    |
+-------+------------+-------------------------+------------+
|     1 | 2019-05-21 | [2019-05-21,2019-05-26) | 2019-05-26 |
+-------+------------+-------------------------+------------+
(1 row)
----------
-- check date '2019-05-28'
with t1 as (
select n+1 as "dt_id"
     , (date '2019-05-21' + interval '5 day' * n)::date as "st_dt"
     , (date '2019-05-21' + interval '5 day' * (n+1))::date as "ed_dt"
  from generate_series(0, 5) as g(n)
), t2 as (
select dt_id
     , st_dt
     , daterange(st_dt, ed_dt, '[)') as "days_range"
  from t1
)
select *
     , upper(days_range) 
  from t2
 where date '2019-05-28' <@ days_range;
 
+-------+------------+-------------------------+------------+
| dt_id |   st_dt    |       days_range        |   upper    |
+-------+------------+-------------------------+------------+
|     2 | 2019-05-26 | [2019-05-26,2019-05-31) | 2019-05-31 |
+-------+------------+-------------------------+------------+
(1 row)

精彩~~

https://ithelp.ithome.com.tw/upload/images/20190521/20050647SBNy6F2fLb.jpg

發好人卡的概念...

我要發表回答

立即登入回答