| Next revision | Previous revision |
| dates [2018/05/13 14:45] – created 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 sql> | <code sql> |
| SELECT GetDate() AS now_date_time, | SELECT GetDate() AS now_date_time, |
| --CAST(FLOOR(CAST(GetDate() AS FLOAT)) AS DATETIME) AS now_date, -- Best method pre 2008 | --CAST(FLOOR(CAST(GetDate() AS FLOAT)) AS DATETIME) AS now_date, -- Best method pre 2008 |
| CAST(GetDate() AS DATE) AS now_date, -- The correct way (new since Sql Server 2008) | CAST(GetDate() AS DATE) AS now_date, -- The correct way (new since Sql Server 2008) |
| DateAdd(mm, DateDiff(m, 0, GetDate()), 0) AS this_month_first_day, -- now - how far through the month we are | DateAdd(mm, DateDiff(m, 0, GetDate()), 0) AS this_month_first_day, -- now - how far through the month we are |
| DateAdd(s, -1, DateAdd(mm, DateDiff(m, 0, GetDate()), 0)) AS last_month_last_day, -- this_month_first_day - 1 second | DateAdd(s, -1, DateAdd(mm, DateDiff(m, 0, GetDate()), 0)) AS last_month_last_day, -- this_month_first_day - 1 second |
| DateAdd(mm, -2, GetDate()) AS two_months_ago_date_time, | DateAdd(mm, -2, GetDate()) AS two_months_ago_date_time, |
| DateAdd(mm, -2, DATEADD(mm, DATEDIFF(m,0,GetDate()),0)) AS two_months_ago_start_date | DateAdd(mm, -2, DATEADD(mm, DATEDIFF(m,0,GetDate()),0)) AS two_months_ago_start_date |
| DatePart(MONTH, DateAdd(s, -1, DateAdd(mm, DateDiff(m, 0, GetDate()), 0))) AS last_month_month_number, | DatePart(MONTH, DateAdd(s, -1, DateAdd(mm, DateDiff(m, 0, GetDate()), 0))) AS last_month_month_number, |
| DatePart(YEAR, DateAdd(s, -1, DateAdd(mm, DateDiff(m, 0, GetDate()), 0))) AS last_month_year_number, | DatePart(YEAR, DateAdd(s, -1, DateAdd(mm, DateDiff(m, 0, GetDate()), 0))) AS last_month_year_number, |
| </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: |