| Both sides previous revision Previous revision Next revision | Previous revision |
| dates [2018/05/13 14:46] – [SQL Server] david | dates [2021/01/12 21:08] (current) – external edit 127.0.0.1 |
|---|
| |
| ====== SQL Server ====== | ====== SQL Server ====== |
| | Pages here: |
| | * [[Creating Date Ranges]] |
| | Other links: |
| * [[http://david-halliday.co.uk/wiki/doku.php?id=databases:sql_server:date_formatting|Date Formatting]] | * [[http://david-halliday.co.uk/wiki/doku.php?id=databases:sql_server:date_formatting|Date Formatting]] |
| * [[http://david-halliday.co.uk/wiki/doku.php?id=databases:sql_server:date_selecting|Date Selecting]] | * [[http://david-halliday.co.uk/wiki/doku.php?id=databases:sql_server:date_selecting|Date Selecting]] |
| </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: |