CODEWITHSUNDEEP
sql-serveruser-defined-functionssql-date-functionssqldatetime
radbyx
radbyx

Reputation: 9670

How to get Last Month interval with MSSQL in January

How can this code be fixed, so it doesn't break in January? I believe this part is the bug:

(Month(Getdate()) - 1

SQL:

AND UOnline.maxstamp > dbo.Udf_converttotimeinterval(
                           Cast(Cast(Year(Getdate()) AS VARCHAR(4))
                           + RIGHT('0' + Cast((Month(Getdate()) - 1) AS VARCHAR(2)), 2)
                           + RIGHT('0' + Cast(1 AS VARCHAR(2)), 2) AS DATETIME), 'ss')

AND UOnline.maxstamp < dbo.Udf_converttotimeinterval(
                           Cast(Cast(Year(Getdate()) AS VARCHAR(4))
                           + RIGHT('0' + Cast(Month(Getdate()) AS VARCHAR(2)), 2)
                           + RIGHT('0' + Cast(1 AS VARCHAR(2)), 2) AS DATETIME), 'ss')

The User Defined Function, if anyone needs it:

USE [XXX]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**** UDF_ConvertToTimeInterval ****/
ALTER FUNCTION [dbo].[UDF_ConvertToTimeInterval](@VALUE DATETIME, 
@INVERVALTYPE VARCHAR(2))
RETURNS BIGINT WITH SCHEMABINDING AS 
BEGIN
    IF @INVERVALTYPE IS NOT NULL
        Begin           
            If Upper(@INVERVALTYPE) = 'S' OR Upper(@INVERVALTYPE) = 'SS'
                RETURN DATEDIFF(SECOND, CONVERT(DATETIME, '01/01/1970', 101), 
                                        CONVERT(DATETIME, @VALUE, 101))
            If Upper(@INVERVALTYPE) = 'MI' OR Upper(@INVERVALTYPE) = 'N'
                RETURN DATEDIFF(MINUTE, CONVERT(DATETIME, '01/01/1970', 101), 
                                        CONVERT(DATETIME, @VALUE, 101))
            If Upper(@INVERVALTYPE) = 'H' OR Upper(@INVERVALTYPE) = 'HH'
                RETURN DATEDIFF(HOUR, CONVERT(DATETIME, '01/01/1970', 101), 
                                      CONVERT(DATETIME, @VALUE, 101))
            If Upper(@INVERVALTYPE) = 'D' OR Upper(@INVERVALTYPE) = 'DD'
                RETURN DATEDIFF(DAY, CONVERT(DATETIME, '01/01/1970', 101), 
                                     CONVERT(DATETIME, @VALUE, 101))
            If Upper(@INVERVALTYPE) = 'M' OR Upper(@INVERVALTYPE) = 'MM'
                RETURN DATEDIFF(MONTH, CONVERT(DATETIME, '01/01/1970', 101), 
                                       CONVERT(DATETIME, @VALUE, 101))
            If Upper(@INVERVALTYPE) = 'Y' OR Upper(@INVERVALTYPE) = 'YY'
                RETURN DATEDIFF(YEAR, CONVERT(DATETIME, '01/01/1970', 101), 
                                      CONVERT(DATETIME, @VALUE, 101))
            Else
                Return Null
        end     
    Else
        Return null
    Return null
END

Upvotes: 0

Views: 663

Answers (1)

Tyron78
Tyron78

Reputation: 4187

Simply substracting from a Month / Day is usually a bad idea... instead of

(Month(Getdate()) - 1

try the following

MONTH(DATEADD(MONTH, -1, GETDATE()))

Upvotes: 1

Related Questions