SQL找出超過六個月的資料

sql

vic1314 2021-12-06 18:47:10 ‧ 2416 瀏覽

分享至

目的:每天會跑一次，找出與現在日期超過滿六個月的資料、然後更新狀態為"超過時間"

問題是假設5/31 往後推會是在12/1才會是滿六個月，希望會是11/30就也抓到5/31
請教先進如何可以

純真的人 iT邦大師 1 級 ‧ 2021-12-06 20:11:38 檢舉

有趣~也就是2/28往後推~應該是9/1會是滿六個月~
先放者~來睡

fillano iT邦超人 1 級 ‧ 2021-12-06 21:58:02 檢舉

https://docs.microsoft.com/zh-tw/sql/t-sql/functions/datediff-transact-sql?view=sql-server-ver15

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

japhenchen

iT邦超人 1 級 ‧ 2021-12-07 08:00:47

1、寫個批次檔:
(最後一行裡的[]內容你自己改)

SET SQL="UPDATE [databaseA].[dbo].[table_RecordsA] SET [State] = '超過時間',[Stat]=LEFT(CONVERT(varchar,getdate(),23),7) ,[UpdatDate] = getdate(),[UpdateUser] = 'check' WHERE convert(char(10),[Date],120) < convert(char(10),dateadd(month, -6, getdate()),120)  and [State]='Null'"


call sqlcmd -S [SQLIP] -d [資料庫名] -Q %SQL% -o output.txt -U [db用戶] -P [db密碼] -u -s "" -w[相容版本，比如180]

2、編進工作排程，比方說每天早上5點....

回應 2
分享
檢舉

japhenchen iT邦超人 1 級 ‧ 2021-12-07 08:02:47 檢舉

另，過期的不用再檢查，省的SQL花時間全部 WHERE一遍

SET SQL="UPDATE [databaseA].[dbo].[table_RecordsA] SET [State] = '超過時間',[Stat]=LEFT(CONVERT(varchar,getdate(),23),7) ,[UpdatDate] = getdate(),[UpdateUser] = 'check' WHERE [State] <> '超過時間' AND convert(char(10),[Date],120) < convert(char(10),dateadd(month, -6, getdate()),120)  and [State]='Null'"

japhenchen iT邦超人 1 級 ‧ 2021-12-07 08:25:17 檢舉

關鍵應該是這句

[Stat]='" & Format(Now, "yyyy-MM").ToString & "'

把今天的年-月值寫進stat，你的語法是VB所以我改成直接SQL語法方便批次檔作業

[Stat]=LEFT(CONVERT(varchar,getdate(),23),7)

登入發表回應

pilipala

iT邦新手 2 級 ‧ 2021-12-07 10:08:41

透過 SQL Server Agent 去排程執行就好啦

回應
分享
檢舉

登入發表回應

純真的人

iT邦大師 1 級 ‧ 2021-12-07 10:25:40

你參考日期判斷調整就好~

declare @Tmp table(
	[Date] datetime
	,[State] nvarchar(50)
	,[Stat] nvarchar(50)
	,[UpdatDate] datetime
	,[UpdateUser] nvarchar(50)
)

insert into @Tmp(
	[Date]
)
values('2021/5/31 12:06:34')
,('2021/4/25 13:16:50')
,('2021/5/25 10:20:35')
,('2021/6/05 18:28:31')
,('2021/9/05 18:28:31')

declare @TestDate date = '2021/11/30'
select (
	case when Month(dateadd(d,1,@TestDate)) <> Month(@TestDate)
	then dateadd(d,-1,dateadd(m,-5,left(Convert(varchar,@TestDate,111),7) + '/1'))
	else dateadd(m,-6,@TestDate)
	end
)

UPDATE @Tmp
SET [State] = N'超過時間'
,[Stat] = Left(convert(varchar,getdate(),120),7)
,[UpdatDate] = getdate()
,[UpdateUser] = N'check'
WHERE isNull([State],'') = ''
and Convert(date,[Date]) <= (
	case when Month(dateadd(d,1,Convert(date,getdate()))) <> Month(Convert(date,getdate()))
	then dateadd(d,-1,dateadd(m,-5,left(Convert(varchar,Convert(date,getdate()),111),7) + '/1'))
	else dateadd(m,-6,Convert(date,getdate()))
	end
)

select *
from @Tmp

這邊有考慮到日期若是2021/2/28~是可以抓到2020/8/31

declare @TestDate date = '2021/2/28'
select (
	case when Month(dateadd(d,1,@TestDate)) <> Month(@TestDate)
	then dateadd(d,-1,dateadd(m,-5,left(Convert(varchar,@TestDate,111),7) + '/1'))
	else dateadd(m,-6,@TestDate)
	end
)