| Both sides previous revision Previous revision Next revision | Previous revision Next revisionBoth sides next revision |
| dates [2018/05/13 14:46] – [SQL Server] david | dates [2018/05/13 15:00] – david |
|---|
| </code> | </code> |
| |
| | Getting whole days, and adding/subtracting days. Below, a selection of ways of getting to whole days. |
| | <code sql> |
| | SELECT GetDate() AS [Now_datetime], |
| | --2005 (and after) |
| | DateAdd(DAY, DateDiff(DAY, 0, GetDate()), 0) AS [today_whole_date], |
| | DateAdd(DAY, -1, DateAdd(DAY, DateDiff(DAY, 0, GetDate()), 0)) AS [yesterday], |
| | DateAdd(DAY, 1, DateAdd(DAY, DateDiff(DAY, 0, GetDate()), 0)) AS [tomorrow], |
| | DateAdd(MONTH, DateDiff(MONTH, 0, GetDate()), 0) AS [start of month], |
| | DateAdd(DAY, -1, DateAdd(MONTH, DateDiff(MONTH, 0, GetDate()), 0)) AS [end of last month], |
| | DateAdd(MONTH, -1, DateAdd(MONTH, DateDiff(MONTH, 0, GetDate()), 0)) AS [start of last month], |
| | DateAdd(MONTH, -3, DateAdd(MONTH, DateDiff(MONTH, 0, GetDate()), 0)) AS [start of 3 months ago], |
| | --2008 (and after) |
| | EOMonth(GetDate()) AS [EOM end of this month], |
| | EOMonth(GetDate(),-1) AS [EOM end of last month], |
| | DateAdd(DAY,1,EOMONTH(GetDate(),-1)) AS [EOM start of this month], |
| | --2014 (and after) -- At last we have DATE as datatype (whole day) |
| | CAST(GetDate()AS DATE) AS [today_whole_date_as_date], |
| | CAST(GetDate() - 1 AS DATE) AS [date_cast_yesterday], |
| | CAST(DateAdd(DAY, -1, GetDate()) AS DATE) AS [date_cast_yesterday_v2], |
| | CAST(DateAdd(MONTH, 1, GetDate()) AS DATE) AS [date_cast_one_month_ahead], |
| | DateFromParts(2017, 01, 01) AS [DFP code a date], |
| | DateAdd(MONTH, -1, DateFromParts(YEAR(GetDate()), MONTH(GetDate()), 1)) AS [DFP Start of last month], |
| | DateAdd(MONTH, -2, DateFromParts(YEAR(GetDate()), MONTH(GetDate()), 1)) AS [DFP Start of month before], |
| | DateAdd(DAY, -1, DateFromParts(YEAR(GetDate()), MONTH(GetDate()), 1)) AS [DFP End of last month] |
| | ; |
| | </code> |
| ====== Oracle ====== | ====== Oracle ====== |
| Links of interest: | Links of interest: |