英文:
How to retrieve real date when passed through CONVERT() function
问题
SELECT
i.owneridname,
CONVERT(varchar, DATEADD(DD,-15,createdon),101) as createdon,
COUNT(i.svb_contactname) AS [是], /* 这是一个条件语句，仅显示非空值 */
COUNT(*) - COUNT(i.svb_contactname) AS [否] /* COUNT(*) 计算空值和非空值，因此这里的减法有效 */
FROM incident i
WHERE i.svb_caseinitiatedfrom IN ('1', '2', '3', '4', '16', '17')
AND i.svb_status_case IN ('1', '2', '12')
GROUP BY
i.owneridname, createdon
ORDER BY
[是] DESC;
英文:
SELECT
i.owneridname,
CONVERT(varchar, DATEADD(DD,-15,createdon),101) as createdon,
COUNT(i.svb_contactname) AS [Yes], /* This is a conditional. It only shows non-null values*/
COUNT(*) - COUNT(i.svb_contactname) AS [No] /* COUNT(*) counts null and non-null, hence why the subtraction here works*/
FROM incident i
WHERE i.svb_caseinitiatedfrom IN ('1', '2', '3', '4', '16', '17')
AND i.svb_status_case IN ('1', '2', '12')
GROUP BY
i.owneridname, createdon
ORDER BY
[Yes] DESC;
The image shows the results (minus the owneridname column) with values passed from the CONVERT function. I was using the DATEADD function to only pull dates up to 15 days previously, yet it's passing all dates in what seems like a text format and not as a 'real' date.
I tried another WHERE clause but it didn't seem to work.
WHERE i.svb_caseinitiatedfrom IN ('1', '2', '3', '4', '16', '17')
AND i.svb_status_case IN ('1', '2', '12')
AND createdon >= DATEADD(DD,-15,createdon)
答案1
得分: 1
你似乎误以为GROUP BY表达式将引用SELECT中的列。事实并非如此。GROUP BY在逻辑上位于SELECT之前（尽管在查询中放置在后面），因此实际上是指的底层列值。如果您给它们起了两个不同的名称，问题就会显而易见。
要获取不带时间的日期，最好只需将其转换为date。在SQL Server 2022和Azure SQL中，使用DATETRUNC(day, i.createdon)代替CAST，因为这与索引更好地配合使用。
还有一点，您似乎希望在WHERE中进行一些日期逻辑。不要在WHERE中对列执行函数，只对要与之进行比较的值执行函数（在本例中是GETDATE）。
SELECT
i.owneridname,
CAST(i.createdon AS date) as createdon,
COUNT(i.svb_contactname) AS [Yes], /* 这是一个条件。它只显示非空值 */
COUNT(*) - COUNT(i.svb_contactname) AS [No] /* COUNT(*)计算null和非null值，因此这里的减法有效 */
FROM incident i
WHERE i.svb_caseinitiatedfrom IN ('1', '2', '3', '4', '16', '17')
AND i.svb_status_case IN ('1', '2', '12')
-- 取当前日期/时间，转换为日期以将时间截断到午夜，减去15天
AND i.createdon >= DATEADD(day, -15, CAST(CAST(GETDATE() AS date) AS datetime))
GROUP BY
i.owneridname,
CAST(i.createdon AS date)
ORDER BY
[Yes] DESC;
英文:
You appear to be laboring under the impression that GROUP BY expressions will refer to the columns in the SELECT. This is not the case. GROUP BY is logically before the SELECT (even though it is placed afterwards in the query), so it is actually referring to the underlying column value. If you had given them two different names then the problem would have been obvious.
To get the date without a time, it's better to just CAST to date. In SQL Server 2022 and Azure SQL, use DATETRUNC(day, i.createdon) instead of CAST, as this works better with indexes.
It also seems you want some date logic in the WHERE. Don't do functions on the columns in the WHERE, only do the functions on the value you want to compare it to (in this case GETDATE).
SELECT
i.owneridname,
CAST(i.createdon AS date) as createdon,
COUNT(i.svb_contactname) AS [Yes], /* This is a conditional. It only shows non-null values*/
COUNT(*) - COUNT(i.svb_contactname) AS [No] /* COUNT(*) counts null and non-null, hence why the subtraction here works*/
FROM incident i
WHERE i.svb_caseinitiatedfrom IN ('1', '2', '3', '4', '16', '17')
AND i.svb_status_case IN ('1', '2', '12')
-- take current date/time, cast to date to truncate the time to midnight, subtract 15 days
AND i.createdon >= DATEADD(day, -15, CAST(CAST(GETDATE() AS date) AS datetime))
GROUP BY
i.owneridname,
CAST(i.createdon AS date)
ORDER BY
[Yes] DESC;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论