在小于10的月份中添加前导零，并将4位年份修剪为2位

Question

我需要打印操纵月份和字符串，我从一个表格中取出，并以类似'12/20'，'11/20'，09/20的格式显示

为此，我需要将年份的最后两位数字和前导零修剪为小于10的月份。

SELECT 
    CAST(MONTH(O.AddDate) AS VARCHAR(2)) + '/' + CAST(YEAR(O.AddDate) AS VARCHAR(4)) AS TimeStamp
FROM 
    [Order] O
WHERE 
    O.CountryCode = 9009
GROUP BY 
    CAST(MONTH(O.AddDate) AS VARCHAR(2)) + '/' + CAST(YEAR(O.AddDate) AS VARCHAR(4))

它提供的输出格式为'10/2020'，'8/2020‘，但我要求它类似于'08/20'，'10/20’

Answer 1

您可以使用format()

select format(O.AddDate, 'MM/yy') as timestamp
from [Order] o
group by format(O.AddDate, 'MM/yy')

显然，这并不是整个查询；否则，如果在select子句中没有聚合函数，则可以使用select distinct而不是group by。

Answer 2

IMHO格式化日期是一个表示层问题。SQL Server应提供数据，表示层应根据需要向用户显示数据。因此，我将像这样编写查询：

SELECT DISTINCT
    YEAR(O.AddDate) AS AddDateYear, MONTH(O.AddDate) AS AddDateMonth
FROM 
    [Order] O
WHERE 
    O.CountryCode = 9009

或者像这样：

SELECT
    YEAR(O.AddDate) AS AddDateYear, MONTH(O.AddDate) AS AddDateMonth
FROM 
    [Order] O
WHERE 
    O.CountryCode = 9009
GROUP BY
   YEAR(O.AddDate), MONTH(O.AddDate)

但是，如果您坚持按照请求格式化日期，则查询如下：

SELECT
   RIGHT('0' + CAST(T.AddDateMonth AS varchar(2)), 2) + '/'
   + RIGHT(CAST(T.AddDateYear) AS varchar(4), 2) AS Y2kVulnerableTimestamp
FROM 
(
    SELECT DISTINCT
        YEAR(O.AddDate) AS AddDateYear, MONTH(O.AddDate) AS AddDateMonth
    FROM 
        [Order] O
    WHERE 
        O.CountryCode = 9009
) T