如何铸造年月日

Question

我有这样的数据：

year (string)(Partitioned) | month (string)(Partitioned) | day (string)(Partitioned) | products

我想将年、月、日只转换为一个日期作为日期，并使用以下查询进行过滤：

SELECT year, month, day, products,
FROM X
WHERE date >= date('2020-05-01')
GROUP BY  1, 2, 3

我该怎么做？

Answer 1

您谈论SQL Server吗？那么到目前为止，转换的最佳字符串格式是"YYYYMMDD"。因此，您可以将日期数字强制转换为字符串，将它们连接起来，然后再次将结果强制转换为一个字符串，如下所示：

SELECT [year], [month], [day], products
FROM X
WHERE CAST (
CAST([year] AS varchar) + 
CAST([month] AS varchar) + 
CAST([day] AS varchar) 
AS date)  >= '20200501'
GROUP BY  1, 2, 3;

您还可以使用看起来更好的DATEFROMPRTS函数：

https://learn.microsoft.com/de-de/sql/t-sql/functions/datefromparts-transact-sql?view=sql-服务器-ver15

SELECT [year], [month], [day], products
FROM X
WHERE DATEFROMPARTS ([year], [month], [day]) >= '20200501'
GROUP BY  1, 2, 3;

否则你可以把它转换成一个数字并进行比较。这是我喜欢的解决方案：

SELECT [year], [month], [day], products
FROM X
WHERE [day] + 100*[month] + 10000*[year] >= 20200501
GROUP BY  1, 2, 3;

Answer 2

您可以尝试：

cast(year || '-' || month || '-' || day as date)