| Both sides previous revision Previous revision Next revision | Previous revisionLast revisionBoth sides next revision |
| dates [2018/05/13 14:55] – david | dates [2018/05/13 16:57] – david |
|---|
| |
| ====== 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> | <code sql> |
| SELECT GetDate() AS [Now_datetime], | SELECT GetDate() AS [Now_datetime], |
| --2005 (and after) | --2005 (and after) |
| DateAdd(DAY, DateDiff(day, 0, GetDate()), 0) AS [today_whole_date], | 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 [yesterday], |
| DateAdd(DAY, 1, DateAdd(day, DateDiff(day, 0, GetDate()), 0)) AS [tomorrow], | DateAdd(DAY, 1, DateAdd(DAY, DateDiff(DAY, 0, GetDate()), 0)) AS [tomorrow], |
| DateAdd(MONTH, DateDiff(month, 0, GetDate()), 0) AS [start of month], | 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(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, -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], | DateAdd(MONTH, -3, DateAdd(MONTH, DateDiff(MONTH, 0, GetDate()), 0)) AS [start of 3 months ago], |
| --2008 (and after) | --2008 (and after) |
| EOMonth(GetDate()) AS [EOM end of this month], | EOMonth(GetDate()) AS [EOM end of this month], |
| --2014 (and after) -- At last we have DATE as datatype (whole day) | --2014 (and after) -- At last we have DATE as datatype (whole day) |
| CAST(GetDate()AS DATE) AS [today_whole_date_as_date], | CAST(GetDate()AS DATE) AS [today_whole_date_as_date], |
| CAST(GetDate() - 1 AS DATE) AS [date_cast_yesterday], | 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], | 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, -1, DateFromParts(YEAR(GetDate()), MONTH(GetDate()), 1)) AS [DFP Start of last month], |