Pages here:

• Creating Date Ranges

Other links:

• Date Formatting
• 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]
;

Links of interest:

• 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