• 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,
;

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