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