In opposite to
Peter's Leow[
^] solution, i'd suggest to use:
- for MS SQL Server 2012 and higher:
DATEFROMPARTS (Transact-SQL)[
^] function
- for earlier versions - a combination of
DATEADD (Transact-SQL)[
^],
GETDATE()[
^],
DATEDIFF()[
^], etc.
For example:
today:
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
yesterday:
DATEADD(day, DATEDIFF(day, 0, GETDATE()), -1)
start of month:
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
end of last month:
DATEADD(month, DATEDIFF(month, 0, GETDATE()), -1)
start of next month:
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 31)
Change it to your needs. Try!
[EDIT]
Tip:
DECLARE @months INT = 12+(4-MONTH(GETDATE())-12)
SELECT DATEADD(month, DATEDIFF(month, 0, DATEADD(month, @months, GETDATE())), -1) AS FiscalYearStart,
DATEADD(month, DATEDIFF(month, 0, DATEADD(month, @months+12, GETDATE())), 0) AS FiscalYearEnd
returns:
FiscalYearStart FiscalYearEnd
2017-03-31 00:00:00.000 2018-04-01 00:00:00.000