This is an old revision of the document!
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
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, ;
Oracle
Links of interest:
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