1

2

3

4

5

SELECT

    OrderID,

    OrderDate,

    DATETRUNC(day, OrderDate) AS 截断到日

FROM #Orders

1

2

3

4

5

6

7

8

-- 按月汇总销售额

SELECT

    DATETRUNC(month, OrderDate) AS 月份,

    COUNT(*)                    AS 订单数,

    SUM(Amount)                 AS 总金额

FROM #Orders

GROUP BY DATETRUNC(month, OrderDate)

ORDER BY 月份

1

2

3

4

-- 以前的写法，需要组合多个函数

GROUP BY DATEADD(month, DATEDIFF(month, 0, OrderDate), 0)

-- 或者

GROUP BY YEAR(OrderDate), MONTH(OrderDate)

1

2

3

4

5

6

7

SELECT

    DATETRUNC(quarter, OrderDate) AS 季度起始日,

    COUNT(*)                      AS 订单数,

    SUM(Amount)                   AS 总金额

FROM #Orders

GROUP BY DATETRUNC(quarter, OrderDate)

ORDER BY 季度起始日

1

2

3

4

5

6

SELECT

    OrderID,

    OrderDate,

    DATETRUNC(week,     OrderDate) AS 本周起始_周日,

    DATETRUNC(iso_week, OrderDate) AS 本周起始_周一

FROM #Orders

1

2

3

4

5

6

SELECT

    OrderID,

    OrderDate,

    DATETRUNC(hour, OrderDate)   AS 截断到小时,

    DATETRUNC(minute, OrderDate) AS 截断到分钟

FROM #Orders

1

2

3

4

5

SELECT

    OrderID,

    OrderDate,

    DATEDIFF(day, DATETRUNC(month, OrderDate), OrderDate) AS 当月第几天

FROM #Orders