近段时间开发的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