How can I get the first date and last date from GETDATE() in SQL Server?-CodePudding

I can get current date from the following query

SELECT GETDATE()

CodePudding user response：

SELECT CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4))  '-' CAST(MONTH(GETDATE()) AS VARCHAR(2)) '-01' AS DATETIME) as FirstDateOfCurrentMonth

SELECT DATEADD(MONTH, 1, CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4))  '-' CAST(MONTH(GETDATE()) AS VARCHAR(2)) '-01' AS DATETIME)) - 1 as LastDateOfCurrentMonth

SELECT CAST(CAST(YEAR(DATEADD(month, -1, GETDATE())) AS VARCHAR(4))  '-' CAST(MONTH(DATEADD(month, -1, GETDATE())) AS VARCHAR(2)) '-01' AS DATETIME) as FirstDateOfPreviousMonth

SELECT DATEADD(MONTH, 1, CAST(CAST(YEAR(DATEADD(month, -1, GETDATE())) AS VARCHAR(4))  '-' CAST(MONTH(DATEADD(month, -1, GETDATE())) AS VARCHAR(2)) '-01' AS DATETIME)) - 1 as LastDateOfPreviousMonth

CodePudding user response：

Not sure what you are looking for. Anyway if you are looking for the first and last days of month and year then this query will help you.

SELECT DATEADD(month, DATEDIFF(month, 0, Getdate()), 0) AS 'First day of Month',
 DATEADD (dd, -1, DATEADD (mm, DATEDIFF (mm, 0, GETDATE ())   1, 0)) as 'Last day of Month',
 DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS 'First day of the year',
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE())   1, -1) AS 'Last day of the year'