Tuesday 20 March 2012

Query to Find First and Last Day of Current Month, Year


DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,
'Last Day of Previous Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
'First Day of Current Month' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,
'Last Day of Current Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,
'First Day of Next Month'

----------


SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, -1, GETDATE())), 0),
            'First Day of Previous Year'
UNION ALL
SELECT      DATEADD(MILLISECOND, -3, DATEADD(YEAR,
            DATEDIFF(YEAR, 0, DATEADD(YEAR, -1, GETDATE())) + 1, 0)),
            'Last Day of Previous Year'
UNION ALL
SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0),
            'First Day of Current Year'
UNION ALL
SELECT      DATEADD(MILLISECOND, -3,
            DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)),
            'Last Day of Current Year'
UNION ALL
SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR,1,GETDATE())), 0),
            'First Day of Next Year'
UNION ALL
SELECT      DATEADD(MILLISECOND, -3,
            DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, 1, GETDATE())) + 1, 0)),
            'Last Day of Next Year'

No comments:

Post a Comment