日期参数SQL

Question

我想编写一个函数，该函数声明@startdate为1月的第一个，但是您运行脚本(当年(的一年，这意味着这一年可以更改，但每天和月份。

有人知道该怎么做吗？

示例代码：

如果@date_type ='第一季度'

开始

set @start_date = 01/01/'当前年份'

设置@end_date = 31/03/'当前年份'

结束

Answer 1

for SQL 2012及以上版本，

SELECT DATEFROMPARTS (YEAR(GETDATE()), 1, 1) 
SELECT DATEFROMPARTS (YEAR(GETDATE()), 03, 31) 

对于所有版本，

SELECT CAST('01 JAN '+CAST(DATEPART(YY,GETDATE()) AS NVARCHAR) AS DATETIME)
SELECT CAST('31 MAR '+CAST(DATEPART(YY,GETDATE()) AS NVARCHAR) AS DATETIME)

Answer 2

我认为您可以做这样的事情(在定义日期而不是隐式转换时最好使用convert(((：

SET @START_DATE = CONVERT(datetime, CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '0101', 112)
    SET @END_DATE = CONVERT(datetime, CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '0331', 112)

Answer 3

如果您使用SQLServer 2012，则可以使用datefromparts

        DECLARE @START_DATE DATE,
                @END_DATE DATE
        SELECT  @START_DATE = DATEFROMPARTS(YEAR(GETDATE()), 1, 1) ,
                @END_DATE = DATEFROMPARTS(YEAR(GETDATE()), 03, 31) 
        SELECT  @START_DATE [start_date] ,
                @END_DATE [end_date]

结果：

    start_date     end_date
    ----------     ----------
    2017-01-01     2017-03-31
    (1 row(s) affected)

Answer 4

DECLARE @Day VARCHAR(10) = '1stQUARTER' --Give the quarter name condiations like 1stQUARTER,2ndQUARTER,3rdQUARTER,4thQuarter 
IF (@Day = '1stQUARTER')
BEGIN
    SELECT CASE 
            WHEN DATENAME(QUARTER, getdate()) - 1 = 1
                THEN DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, 0)
            END AS StartDayQuarter
        ,CASE 
            WHEN DATENAME(QUARTER, getdate()) - 1 = 1
                THEN DATEADD(dd, - 1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0))
            END AS EndDayQuarter
END
ELSE IF (@Day = '2ndQUARTER')
BEGIN
    SELECT CASE 
            WHEN DATENAME(QUARTER, getdate()) = 2
                THEN DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)
            END AS FirstDayQuarter
        ,CASE 
            WHEN DATENAME(QUARTER, getdate()) = 2
                THEN DATEADD(mm, + 3, DATEADD(dd, - 1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)))
            END AS LasttDayQuarter
END
ELSE IF (@Day = '3rdQUARTER')
BEGIN
    SELECT CASE 
            WHEN DATENAME(QUARTER, getdate()) + 1 = 3
                THEN DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 1, 0)
            END AS FirstDayQuarter
        ,CASE 
            WHEN DATENAME(QUARTER, getdate()) + 1 = 3
                THEN DATEADD(mm, + 6, DATEADD(dd, - 1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)))
            END AS LasttDayQuarter
END
ELSE IF (@Day = '4thQuarter')
BEGIN
    SELECT CASE 
            WHEN DATENAME(QUARTER, getdate()) + 2 = 4
                THEN DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 2, 0)
            END AS FirstDayQuarter
        ,CASE 
            WHEN DATENAME(QUARTER, getdate()) + 2 = 4
                THEN DATEADD(mm, + 9, DATEADD(dd, - 1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)))
            END AS LasttDayQuarter
END
ELSE
BEGIN
    PRINT 'Enter Valid Quarter Name'
END

输出

   FirstDayQuarter      |LasttDayQuarter
--------------------------------------------
2017-01-01 00:00:00.000 |2017-03-31 00:00:00.000

Answer 5

在SQL 2012 中，您可以使用datefromparts

IF @DATE_TYPE = 'FIRST QUARTER'
BEGIN
   SET @START_DATE = datefromparts(year(getdate()), 1,1)
   SET @END_DATE = datefromparts(year(getdate()), 3,31)
END

如果要获得FISRT日期和季度的最后日期，请使用

DECLARE @CurrentDate datetime = '2017-07-01' -- or getdate() or any date
SELECT  datefromparts(year(@CurrentDate), 
                 datepart(quarter, @CurrentDate) * 3 - 2,1) AS StartDate,
        EOMONTH(datefromparts(year(@CurrentDate), 
                 datepart(quarter, @CurrentDate) * 3 , 1)) AS EndDate

Answer 6

DECLARE @START_DATE DATETIME
DECLARE @END_DATE DATETIME
DECLARE @DATE_TYPE VARCHAR(500)
DECLARE @YEAR VARCHAR(4)
SET @DATE_TYPE = 'FIRST QUARTER' -- SAMPLE PARAMETER
SET @YEAR = CAST(YEAR(GETDATE()) AS VARCHAR(4))
DECLARE @tbl TABLE ([QUARTER] VARCHAR(50), StartMonth VARCHAR(2), EndMonth VARCHAR(2))
INSERT INTO @tbl ([QUARTER], StartMonth, EndMonth)
SELECT 'FIRST QUARTER', '1', '3'
UNION ALL
SELECT 'SECOND QUARTER', '4', '6'
UNION ALL
SELECT 'THIRD QUARTER', '7', '9'
UNION ALL
SELECT 'FOURTH QUARTER', '10', '12'


SELECT @START_DATE = StartMonth + '/1/' + @YEAR,
    @END_DATE = DATEADD(DD, -1, (DATEADD(mm, 1, EndMonth + '/1/' + @YEAR)))
FROM @tbl
WHERE [QUARTER] = @DATE_TYPE