USE tempdb GO IF OBJECT_ID('pGetBdEd') IS NOT NULL DROP PROC pGetBdEd GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE pGetBdEd @curdate SMALLDATETIME='2018-10-30', @bd SMALLDATETIME output, @ed SMALLDATETIME output AS BEGIN SET NOCOUNT ON; --下面针对不同的版本,写了两种方法,你选择一种即可 --sqlserver2012+ SET @bd=CAST(EOMONTH(@curdate,-1) AS SMALLDATETIME)+1 SET @ed=EOMONTH(@curdate)
--sqlserver2005+ SET @bd=CONVERT(CHAR(8),@curdate,120)+'01' SET @ed=CAST(CONVERT(CHAR(8),DATEADD(MONTH,1,@curdate),120)+'01' AS SMALLDATETIME)-1 END GO--调用 DECLARE @bd SMALLDATETIME,@ed SMALLDATETIME EXEC pGetBdEd @curdate ='2018-10-30', @bd=@bd OUTPUT, @ed=@ed OUTPUTSELECT @bd AS [月头],@ed AS [月末] /* 月头 月末 ----------------------- ----------------------- 2018-10-01 00:00:00 2018-10-31 00:00:00 */
--A. 月的第一天
SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')--B. 月的最后一天
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')
GO
IF OBJECT_ID('pGetBdEd') IS NOT NULL DROP PROC pGetBdEd
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE pGetBdEd
@curdate SMALLDATETIME='2018-10-30',
@bd SMALLDATETIME output,
@ed SMALLDATETIME output
AS
BEGIN
SET NOCOUNT ON;
--下面针对不同的版本,写了两种方法,你选择一种即可
--sqlserver2012+
SET @bd=CAST(EOMONTH(@curdate,-1) AS SMALLDATETIME)+1
SET @ed=EOMONTH(@curdate)
--sqlserver2005+
SET @bd=CONVERT(CHAR(8),@curdate,120)+'01'
SET @ed=CAST(CONVERT(CHAR(8),DATEADD(MONTH,1,@curdate),120)+'01' AS SMALLDATETIME)-1
END
GO--调用
DECLARE @bd SMALLDATETIME,@ed SMALLDATETIME
EXEC pGetBdEd
@curdate ='2018-10-30',
@bd=@bd OUTPUT,
@ed=@ed OUTPUTSELECT @bd AS [月头],@ed AS [月末]
/*
月头 月末
----------------------- -----------------------
2018-10-01 00:00:00 2018-10-31 00:00:00
*/