{"id":78,"date":"2012-08-21T20:52:00","date_gmt":"2012-08-22T01:52:00","guid":{"rendered":"https:\/\/www.chrystalsander.com\/blog\/?p=78"},"modified":"2023-07-26T12:38:15","modified_gmt":"2023-07-26T17:38:15","slug":"sql-server-get-first-and-last-days-of-month","status":"publish","type":"post","link":"https:\/\/www.chrystalsander.com\/blog\/?p=78","title":{"rendered":"SQL Server: Get First and Last Days Of Month"},"content":{"rendered":"
\n
\nDECLARE<\/span> @min DATETIME<\/u><\/u><\/span><\/span><\/div>\n
\nDECLARE<\/span> @max DATETIME<\/u><\/u><\/span><\/span><\/div>\n
\nDECLARE<\/span> @now DATETIME<\/u><\/u><\/span><\/span><\/div>\n
\n<\/div>\n
\nSET<\/span> @now =<\/span> GETDATE<\/span>();<\/span><\/span><\/div>\n
\n<\/div>\n

<\/span><\/span>–get first day of last month:<\/span><\/p>\n

\nSET<\/span> @min =<\/span> CONVERT<\/span>(<\/span>VARCHAR<\/span>(<\/span>25),<\/span>DATEADD<\/span>(<\/span>dd<\/wbr><\/span><\/span><\/div>\n<\/div>\n

,-(<\/span><\/span>DAY<\/span>(<\/span>DATEADD<\/span>(<\/span>mm<\/span><\/span>,<\/span>1<\/span><\/span>,<\/span>@now<\/span><\/span>))-<\/span>1<\/span><\/span>),<\/span><\/wbr><\/span>DATEADD<\/span><\/span>(<\/span>mm<\/span><\/span>,-<\/span>1<\/span><\/span>,<\/span>@now<\/span><\/span>)),<\/span>101<\/span><\/span>)<\/span> <\/span><\/span><\/p>\n

–get the last day of last month<\/span><\/p>\n

\nSET<\/span> @max =<\/span> CONVERT<\/span>(<\/span>VARCHAR<\/span>(<\/span>25),<\/span>DATEADD<\/span>(<\/span>dd<\/wbr><\/span><\/span><\/div>\n

,-(<\/span><\/span>DAY<\/span>(<\/span>@now<\/span><\/span>)),<\/span>@now<\/span><\/span>),<\/span>101<\/span><\/span>)<\/span> <\/span><\/span><\/p>\n

\n<\/div>\n
\n–get the first day of current month<\/span> <\/span><\/div>\n
\nSET<\/span> @min =<\/span> CONVERT<\/span>(<\/span>VARCHAR<\/span>(<\/span>25),<\/span>DATEADD<\/span>(<\/span>dd<\/wbr><\/span><\/span><\/div>\n

,-(<\/span><\/span>DAY<\/span>(<\/span>@now<\/span><\/span>)-<\/span>1<\/span><\/span>),<\/span>@now<\/span><\/span>),<\/span>101<\/span><\/span>)<\/span>  <\/span><\/span><\/p>\n

<\/span><\/span>–get the last day of current month<\/span> <\/span><\/span><\/p>\n

\nSET<\/span> @max =<\/span> CONVERT<\/span>(<\/span>VARCHAR<\/span>(<\/span>25),<\/span>DATEADD<\/span>(<\/span>dd<\/wbr><\/span><\/span><\/div>\n

,-(<\/span><\/span>DAY<\/span>(<\/span>DATEADD<\/span>(<\/span>mm<\/span><\/span>,<\/span>1<\/span><\/span>,<\/span>@now<\/span><\/span>))),<\/span>DA<\/wbr><\/span>TEADD<\/span><\/span>(<\/span>mm<\/span><\/span>,<\/span>1<\/span><\/span>,<\/span>@now<\/span><\/span>)),<\/span>101<\/span><\/span>)<\/span> <\/span><\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

DECLARE @min DATETIME DECLARE @max DATETIME DECLARE @now DATETIME SET @now = GETDATE(); –get first day of last month: SET @min = CONVERT(VARCHAR(25),DATEADD(dd ,-(DAY(DATEADD(mm,1,@now))-1),DATEADD(mm,-1,@now)),101)  –get the last day of last month SET @max = CONVERT(VARCHAR(25),DATEADD(dd ,-(DAY(@now)),@now),101)  –get the first day of current month SET @min = CONVERT(VARCHAR(25),DATEADD(dd ,-(DAY(@now)-1),@now),101)   –get the last day of current month … Continue reading “SQL Server: Get First and Last Days Of Month”<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[50],"_links":{"self":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/78"}],"collection":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=78"}],"version-history":[{"count":1,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/78\/revisions"}],"predecessor-version":[{"id":194,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/78\/revisions\/194"}],"wp:attachment":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=78"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=78"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=78"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}