dates [wiki.roman-halliday.com]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
Dates are a big thing in computing and databases.

I'll throw notes here and clean them up some time in the future, probably some time never.

====== 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_selecting|Date Selecting]]
<code sql>
SELECT GetDate()                                                   AS now_date_time,
     --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)
       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(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
       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,
;
</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 ======
Links of interest:
  * [[http://david-halliday.co.uk/wiki/doku.php?id=databases:oracle:interval_data_type|interval data type]]


<code sql>
SELECT date_field "date_pk",
       to_char(date_field,'yyyy-mm-dd') "date",
       to_number(to_char(date_field,'yyyy')) "year",
       to_number(to_char(date_field,'yyyy-q')) "hierarchy_quarter",
       to_number(to_char(date_field,'q')) "quarter",
       to_number(to_char(date_field,'yyyy-mm')) "hierarchy_month",
       to_number(to_char(date_field,'mm')) "month",
       to_char(date_field,'month') "month_name",
       to_number(to_char(date_field,'yyyy-mm-DD')) "hierarchy_day",
       to_number(to_char(date_field,'DD')) "day",
       to_number(to_char(date_field,'yyyy-WW')) "hierarchy_week",
       to_number(to_char(date_field,'WW')) "week"
  FROM schema.table
GROUP BY date_field 
ORDER BY date_field
</code>