iT邦幫忙

0

SQL找出超過六個月的資料

sql

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

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

有趣~也就是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
4
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點....

另,過期的不用再檢查,省的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'"

關鍵應該是這句

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

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

[Stat]=LEFT(CONVERT(varchar,getdate(),23),7)
1
pilipala
iT邦新手 4 級 ‧ 2021-12-07 10:08:41

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

1
純真的人
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

https://ithelp.ithome.com.tw/upload/images/20211207/200613690RTSv9wNG4.png

這邊有考慮到日期若是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
)

https://ithelp.ithome.com.tw/upload/images/20211207/20061369bfP3ZRnDGh.png

我要發表回答

立即登入回答