首先,提前感谢所有回答这个问题的人。
我在SQL Server 2012 Express中有一个简单的SQL Server视图(在现有表上)。该视图有两个感兴趣的列,用于(打印)报告目的。
这两列的数据类型均为DATETIME
。一个是用于Departure
日期时间,另一个是Arrival
日期时间。
现在,我的问题是这些人。SELECT
子句的语法是什么,这样,如果其中一个日期时间列不等于今天,则视图可以创建一个新列作为空白字符串?
因此,例如,如果ArrivalDate
不等于今天,我希望不仅返回存储在ArrivalDate
中的日期,,还返回一个BLANK字符串
我已经为现在的视图包含了SELECT
声明:
SELECT
bookingRef, custName, departureDateTime, arrivalDateTime
FROM
dbo.Bookings
WHERE
(departureDateTime BETWEEN CAST(GETDATE() AS DATE)
AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE)))
OR (arrivalDateTime BETWEEN CAST(GETDATE() AS DATE)
AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE)))
我是否记得这项任务需要变量/参数利用???
所有String数据类型中的Right first可以具有Empty字符串,但Date数据类型不能具有空日期值,它可以具有日期值或NULL
值。
您可以返回一个伪日期,如1900-01-01或NULL。但是在日期时间/日期列中不能有空字符串。
所以你可以做这样的事情。。。
SELECT bookingRef
, custName
, CASE WHEN CAST(departureDateTime AS DATE) <> CAST(GETDATE() AS DATE)
THEN NULL
ELSE departureDateTime END AS departureDateTime
, CASE WHEN CAST(arrivalDateTime AS DATE) <> CAST(GETDATE() AS DATE)
THEN NULL
ELSE arrivalDateTime END AS arrivalDateTime
FROM dbo.Bookings
WHERE CAST(departureDateTime AS DATE) = CAST(GETDATE() AS DATE)
OR CAST(arrivalDateTime AS DATE) = CAST(GETDATE() AS DATE)
或者,可以以字符串/字符数据类型返回日期时间列,然后在最终结果中使用空字符串。你可以做这样的事。。。
SELECT bookingRef
, custName
, CASE WHEN CAST(departureDateTime AS DATE) <> CAST(GETDATE() AS DATE)
THEN ''
ELSE CONVERT(VARCHAR(19), departureDateTime, 120)
END AS departureDateTime
, CASE WHEN CAST(arrivalDateTime AS DATE) <> CAST(GETDATE() AS DATE)
THEN ''
ELSE CONVERT(VARCHAR(19), arrivalDateTime, 120)
END AS arrivalDateTime
FROM dbo.Bookings
WHERE CAST(departureDateTime AS DATE) = CAST(GETDATE() AS DATE)
OR CAST(arrivalDateTime AS DATE) = CAST(GETDATE() AS DATE)