在最近的項(xiàng)目開發(fā)過程中，遇到了Sql server自動分割月的功能需求，這里在網(wǎng)上整理下資料.
1、為何出現(xiàn)自定義分割月的需求
今天梳理一個(gè)平臺的所有函數(shù)時(shí)，發(fā)現(xiàn)了一個(gè)自定義分割月函數(shù)，也就是指定分割月的開始日索引值（可以從1-31閉區(qū)間內(nèi)的任何一個(gè)值）來獲取指定日期所對應(yīng)的分割月數(shù)值。這個(gè)函數(shù)當(dāng)時(shí)是為了解決業(yè)務(wù)部門獲取非標(biāo)準(zhǔn)月（標(biāo)準(zhǔn)月就是從每個(gè)月的第一天到最后一天組成一個(gè)完成的標(biāo)準(zhǔn)月份）的統(tǒng)計(jì)匯總數(shù)據(jù)的。例如：如果指定分割月的開始日索引值為5則表示某個(gè)月的5號到下個(gè)月的4號之間作為一個(gè)完整的分割月；同樣地如果指定分割月的開始日索引值為1則表示標(biāo)準(zhǔn)月等等。
我仔細(xì)梳理了這個(gè)函數(shù)進(jìn)行了重構(gòu)簡化以及擴(kuò)展，該自定義分割月函數(shù)的實(shí)現(xiàn)區(qū)別之前寫的SQL Server時(shí)間粒度系列----第3節(jié)旬、月時(shí)間粒度詳解文章中將一個(gè)整數(shù)值和月份日期相互轉(zhuǎn)換功能，這個(gè)是按照標(biāo)準(zhǔn)月來實(shí)現(xiàn)的，雖然思路大致相同，但是并沒有針對之前的月份日期和整數(shù)值轉(zhuǎn)換函數(shù)對來進(jìn)行擴(kuò)展而是獨(dú)立開發(fā)新的功能函數(shù)。也是為了盡量做到函數(shù)功能職責(zé)單一性、穩(wěn)定性、可維護(hù)性以及可擴(kuò)展性。
2、sql server實(shí)現(xiàn)自定義分割月功能
自定義分割月功能函數(shù)包括兩個(gè)標(biāo)量函數(shù)：ufn_SegMonths和ufn_SegMonth2Date。ufn_SegMonths獲取指定的日期在自定義分割月對應(yīng)的分割月數(shù)值；ufn_SegMonth2Date獲取指定一個(gè)分割月數(shù)值賭對應(yīng)的月份日期。
sql server 版本的實(shí)現(xiàn)T-SQL代碼如下：
IF OBJECT_ID(N'[dbo].[ufn_SegMonths]', 'FN') IS NOT NULL
BEGIN
DROP FUNCTION [dbo].[ufn_SegMonths];
END
GO
--==================================
-- 功能：根據(jù)自定義月開始索引值獲取指定日期所在的自定義月數(shù)。
-- 說明：自定義分割月數(shù) = 年整數(shù)值*100 + 當(dāng)前所在分割月值。
-- 環(huán)境：SQL Server 2005+。
-- 調(diào)用：SET @intSegMonths = dbo.fn_SegMonths('2008-01-14', 15)。
-- 創(chuàng)建：XXXX-XX-XX XX:XX-XX:XX XXX 創(chuàng)建函數(shù)實(shí)現(xiàn)。
-- 修改：XXXX-XX-XX XX:XX-XX:XX XXX XXXXXXXX。
--==================================
CREATE FUNCTION [dbo].[ufn_SegMonths]
(
@dtmDate AS DATETIME -- 日期
,@tntSegStartIndexOfMonth AS INT = 15 -- 自定義分割月開始索引值（1-31)
)
RETURNS INT
AS
BEGIN
IF (@tntSegStartIndexOfMonth = 0 OR @tntSegStartIndexOfMonth >= 32)
BEGIN
SET @tntSegStartIndexOfMonth = 15;
END
DECLARE
@intYears AS INT
,@tntMonth AS TINYINT
,@sntDay AS SMALLINT;
SELECT
@intYears = DATEDIFF(YEAR, '1900-01-01', @dtmDate)
,@tntMonth = DATEPART(MONTH, @dtmDate)
,@sntDay = DATEPART(DAY, @dtmDate);
IF (@sntDay >= @tntSegStartIndexOfMonth)
BEGIN
SET @tntMonth = @tntMonth + 1;
END
IF (@tntMonth > 12)
BEGIN
SELECT
@intYears = @intYears + 1
,@tntMonth = @tntMonth - 12;
END
RETURN @intYears * 100 + @tntMonth;
END
GO
IF OBJECT_ID(N'[dbo].[ufn_SegMonths2Date]', 'FN') IS NOT NULL
BEGIN
DROP FUNCTION [dbo].[ufn_SegMonths2Date];
END
GO
--==================================
-- 功能：獲取自定義分割月數(shù)對應(yīng)的自定義分割月日期。
-- 說明：自定義分割月日期 = 自定義分割月數(shù)/100對應(yīng)的年整數(shù)日期“組合”當(dāng)前所在分割月值。
-- 環(huán)境：SQL Server 2005+。
-- 調(diào)用：SET @dtmSegMonthDate = dbo.fn_SegMonths2Date(11602)。
-- 創(chuàng)建：XXXX-XX-XX XX:XX-XX:XX XXX 創(chuàng)建函數(shù)實(shí)現(xiàn)。
-- 修改：XXXX-XX-XX XX:XX-XX:XX XXX XXXXXXXX。;
--==================================
CREATE FUNCTION [dbo].[ufn_SegMonths2Date]
(
@intSegMonths AS INT -- 自定義分割月數(shù)
)
RETURNS DATETIME
AS
BEGIN
DECLARE @dtmDefaultBasedate AS DATETIME;
SET @dtmDefaultBasedate = '1900-01-01';
IF ((@intSegMonths IS NULL) OR (@intSegMonths = 0))
BEGIN
RETURN @dtmDefaultBasedate;
END
DECLARE
@intYears AS INT
,@intMonth AS INT;
SELECT
@intYears = @intSegMonths / 100
,@intMonth = @intSegMonths % 100;
RETURN DATEADD(MONTH, @intMonth - 1, DATEADD(YEAR, @intYears, @dtmDefaultBasedate));
END
GO
3、測試驗(yàn)證效果
針對以上簡單的測試代碼如下：
DECLARE
@dtmStartDate AS DATETIME
,@dtmEndDate AS DATETIME;
SELECT
@dtmStartDate = '2000-01-01'
,@dtmEndDate = '2016-12-31';
SELECT
[T1].*
,[dbo].[ufn_SegMonths2Date]([T1].[SegMonths]) AS SegMonthDate
FROM (
SELECT
[T].[CDate]
,[dbo].[ufn_SegMonths]([T].[CDate], 28) AS SegMonths
FROM (
SELECT
DATEADD(DAY, [Num], @dtmStartDate) AS CDate
FROM
[dbo].[ufn_GetNums](0, DATEDIFF(DAY, @dtmStartDate, @dtmEndDate))
) AS T
WHERE [T].[CDate] BETWEEN '2014-12-01' AND '2016-03-31'
) AS T1
WHERE DATEPART(DAY, [T1].[CDate]) >= 27
GO
效果截圖如下：
注意：以上測試代碼使用了SQL Server數(shù)字輔助表的實(shí)現(xiàn)這邊文章的內(nèi)聯(lián)表值函數(shù)ufn_GetNums。
4、總結(jié)語
這次是梳理平臺的功能性函數(shù)所進(jìn)行的重構(gòu)簡化以及擴(kuò)展的實(shí)現(xiàn)。盡量將日期有關(guān)的功能函數(shù)梳理出來，便于直接在sql server用戶數(shù)據(jù)庫中來使用， 也便于BI倉庫中使用。國慶一來已經(jīng)過去一周，原來打算一周一遍的計(jì)劃還是延期啦，再次嚴(yán)重檢討自己。
感謝閱讀，希望能幫助到大家，謝謝大家對本站的支持！
您可能感興趣的文章:- SqlServer中如何解決session阻塞問題
- SqlServer 英文單詞全字匹配詳解及實(shí)現(xiàn)代碼
- SqlServer編寫數(shù)據(jù)庫表的操作方式(建庫、建表、修改語句)
- C#訪問SQLServer增刪改查代碼實(shí)例
- SqlServer查詢和Kill進(jìn)程死鎖的語句
- 獲取SqlServer存儲過程定義的三種方法
- SQLServer主鍵和唯一約束的區(qū)別
- SqlServer存儲過程實(shí)現(xiàn)及拼接sql的注意點(diǎn)
- win2008 r2 服務(wù)器php+mysql+sqlserver2008運(yùn)行環(huán)境配置（從安裝、優(yōu)化、安全等）
- SQLSERVER簡單創(chuàng)建DBLINK操作遠(yuǎn)程服務(wù)器數(shù)據(jù)庫的方法
- SqlServer2008誤操作數(shù)據(jù)(delete或者update)后恢復(fù)數(shù)據(jù)的方法
