SQL：具有不同类型参数的 ISNULL 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7963652/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: ISNULL function with different type of parameters
提问by Soner G?nül
In SQL Server, ISNULL()function has to same type of parameters.
在 SQL Server 中，ISNULL()函数必须使用相同类型的参数。
check_expression
Is the expression to be checked for NULL. check_expression can be of any type.
replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.
检查表达式
是要检查 NULL 的表达式。check_expression 可以是任何类型。
替换值
如果 check_expression 为 NULL，则是要返回的表达式。replacement_value必须与 check_expresssion 具有相同的类型。
How can I use it with different type of parameter? I want to use with dateand stringparameter like this ISNULL(A.DATE, '-')
如何将它与不同类型的参数一起使用？我想像这样使用 withdate和string参数ISNULL(A.DATE, '-')
NOTE: Type of A.DATEis datetime.
注意：类型A.DATE是datetime。
EDIT-1: My full query which is getting 0 row:
EDIT-1：我的完整查询获得 0 行：
SELECT A.pkey as KRED?, A.SUMMARY , D.BayiStatu AS STATU, D.Sorumlu AS SORUMLU, C.BayiSonuc as SONUC, ISNULL( CONVERT(VARCHAR(25), A.CREATED, 112) , '-' ), ISNULL( CONVERT(VARCHAR(25), A.RESOLUTIONDATE, 112) , '-' ), dbo.CUSTVAL(11931, A.ID, 'S') AS BAY?, ISNULL( CONVERT(VARCHAR(25), dbo.GetLastStatuTime(A.ID), 112) , '-' ) AS SON_STATU_TAR,j2.SUMMARY, ISNULL( CONVERT(VARCHAR(25), j2.CREATED, 112) , '-' ), ISNULL( CONVERT(VARCHAR(25), j2.RESOLUTIONDATE, 112) , '-' ), j3.SUMMARY, ISNULL( CONVERT(VARCHAR(25), j3.CREATED, 112) , '-' ), ISNULL( CONVERT(VARCHAR(25), j3.RESOLUTIONDATE, 112) , '-' )
FROM AspNetServicesDB.dbo.SONUC_MAP C, J?RA.resolution E, jira.issuestatus B, AspNetServicesDB.dbo.STATU_MAP D, Jira.jiraissue A
INNER JOIN Jira.issuelink i
ON i.SOURCE = A.ID and i.SEQUENCE = 0
INNER JOIN Jira.jiraissue As j2
ON i.DESTINATION =j2.ID
LEFT JOIN Jira.issuelink i2
ON i2.SOURCE = A.ID and i2.SEQUENCE = 1
LEFT JOIN Jira.jiraissue As j3
ON i2.DESTINATION = j3.ID
WHERE A.issuestatus = B.ID
AND 'BA?ARAN OTOMAT?V' = dbo.CUSTVAL(11931, A.ID, 'S')
AND B.pname = D.JiraStatu collate Turkish_CS_AI
AND A.issuetype != 11
AND A.RESOLUTION = E.ID
AND E.pname = C.JiraSonuc collate Turkish_CS_AI
EDIT-2: But this working
EDIT-2：但是这个工作
select ISNULL( CONVERT(VARCHAR(25), A.RESOLUTIONDATE, 112) , '-' )
FROM Jira.jiraissue A
Could be the reason JOIN?
可能是原因JOIN吗？
回答by GolezTrol
You can't. The ISNULL function is used by itself as a query result column, or in an expression that eventually is a column in the query result. All fields/rows in a column must have the same data type. So you'll have to choose.
你不能。ISNULL 函数本身用作查询结果列，或在最终成为查询结果中的列的表达式中使用。列中的所有字段/行必须具有相同的数据类型。所以你必须做出选择。
One solution would be to cast the DATE to string, so the result is always a string, but i feel the bestsolution would be to return NULL for empty dates and let the presentation layer decide whether or not the NULL dates should be shown as -and in what format the non-null dates should be displayed (client locale settings).
一种解决方案是将 DATE 转换为字符串，因此结果始终是一个字符串，但我觉得最好的解决方案是为空日期返回 NULL 并让表示层决定是否应将 NULL 日期显示为-和应以何种格式显示非空日期（客户端区域设置）。
With presentation layer, I mean anything that displays or outputs this data, which can be a web page, a CSV exporter, a reporting tool, whatever.
对于表示层，我的意思是任何显示或输出此数据的东西，可以是网页、CSV 导出器、报告工具等等。
回答by Code Magician
Can you simply cast your date as a varchar? Since you're output may not be a valid date anyway, this should work just fine:
您可以简单地将日期转换为 varchar 吗？由于您的输出无论如何都可能不是有效日期，因此这应该可以正常工作：
ISNULL(CONVERT(varchar(10), A.Date, 101),'-')
回答by Mikael Eriksson
You need to cast the date to a string if you really want a -instead of null.
如果你真的想要一个-而不是 ，你需要将日期转换为一个字符串null。
declare @T table(adate datetime)
insert into @T values(null)
insert into @T values(getdate())
select isnull(convert(varchar(23), adate, 126), '-')
from @T
回答by Bogdan Sahlean
I would convert DATE values from [DATE]TIMEto VARCHARusing CONVERT/CAST:
我会使用CONVERT/CAST将 DATE 值从 转换[DATE]TIME为：VARCHAR
SELECT ... ISNULL( CONVERT(VARCHAR(25), A.DATE, 112) , '-' ) AS DATE
FROM ...
Instead of 112 style (yyymmdd) can be used another style: 103 (dd/mm/yyyy), 101 (mm/dd/yyyy) or another style from Date and Time Stylessection.
可以使用另一种样式代替 112 样式 (yyymmdd)：103 (dd/mm/yyyy)、101 (mm/dd/yyyy) 或来自Date and Time Stylessection 的其他样式。
回答by ypercube??
You can try:
你可以试试：
CASE WHEN A.DATE IS NULL
THEN '-'
ELSE CONVERT(VARCHAR(25), A.DATE, 112)
END
