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]]
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,
;
Getting whole days, and adding/subtracting days. Below, a selection of ways of getting to whole days.
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]
;
====== Oracle ======
Links of interest:
* [[http://david-halliday.co.uk/wiki/doku.php?id=databases:oracle:interval_data_type|interval data type]]
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