MS SQL Server Quarter Function

近段时间开发的ERP系统，需要涉及至季度的一些日期。在系统中，实现了三个函数。

 

获取某一天的所在季度的第一天：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:         Insus.NET
-- Create date:    2012-08-24
-- Description:    获取某一天所在季度的第一天。
-- =============================================
CREATE FUNCTION [dbo].[udf_FirstDayOfQuarter] 
(
     @Date DATETIME
 )
RETURNS DATETIME
BEGIN    
RETURN CAST(YEAR(@Date) AS VARCHAR(4)) + CASE WHEN MONTH(@Date) IN ( 1,  2,  3) THEN '-01-01'
                                              WHEN MONTH(@Date) IN ( 4,  5,  6) THEN '-04-01'
                                              WHEN MONTH(@Date) IN ( 7,  8,  9) THEN '-07-01'
                                              WHEN MONTH(@Date) IN (10, 11, 12) THEN '-10-01'
END
END

 

获取某一天所在季度的最后一天：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:         Insus.NET
-- Create date:    2012-08-24
-- Description:    获取某一天所在季度的最后一天。
-- =============================================
CREATE FUNCTION [dbo].[udf_LastDayOfQuarter] 
(
     @Date DATETIME
 )
RETURNS DATETIME
BEGIN    
RETURN CAST(YEAR(@Date) AS VARCHAR(4)) + CASE WHEN MONTH(@Date) IN ( 1,  2,  3) THEN '-03-31'
                                              WHEN MONTH(@Date) IN ( 4,  5,  6) THEN '-06-30'
                                              WHEN MONTH(@Date) IN ( 7,  8,  9) THEN '-09-30'
                                              WHEN MONTH(@Date) IN (10, 11, 12) THEN '-12-31'
END
END

 

 季度函数：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:         Insus.NET
-- Create date:    2012-08-24
-- Description:    季度函数。
-- =============================================
ALTER FUNCTION [dbo].[udf_Quarter]
( 
   @Year INT
)
RETURNS @t TABLE ([Quarter] TINYINT,[FirstDate] DATETIME,[LastDate] DATETIME)
BEGIN
INSERT INTO @t ([Quarter],[FirstDate],[LastDate]) VALUES (1, CAST(@Year AS VARCHAR(4)) + '-01-01',CAST(@Year AS VARCHAR(4)) + '-03-31'),
                                                         (2, CAST(@Year AS VARCHAR(4)) + '-04-01',CAST(@Year AS VARCHAR(4)) + '-06-30'),
                                                         (3, CAST(@Year AS VARCHAR(4)) + '-07-01',CAST(@Year AS VARCHAR(4)) + '-09-30'),
                                                         (4, CAST(@Year AS VARCHAR(4)) + '-10-01',CAST(@Year AS VARCHAR(4)) + '-12-31')

RETURN 
END