开始日期和结束日期在SQL Server视图中显示为单个字段 [英] Start date and end date show in SQL server view as single field

问题描述

我想在SQL服务器中创建视图，并在此视图中将开始日期和结束日期显示为单列如何实现，我尝试了下面给出的代码并给出错误日期和varchar在add运算符中不兼容

我的第二个问题是，在视图中使用主要关键



我尝试过：

 CREATE VIEW [V_SalesReportLocked] AS 
 SELECT SRLID，DateFrom +'To'+ DateTo 
 FROM Reports.SalesReportLocked 
 WHERE IsActive = 1和IsClosed = 0 

解决方案

正如其他人提到的那样，当您将数据转换为varchar时，您可以连接这些列。 br />


但是，我不建议在SQL查询中以这种方式格式化数据。根据您使用EF的主键错误消息。如果这是正确的，我更愿意将列作为日期时间字段添加到模型中，然后将计算字段添加到实体中以显示所需格式的日期。换句话说，格式化将在客户端完成。



原因是SQL不是为格式化数据而设计的，主要用途是数据操作和恢复。另一件事是，如果格式化取决于客户端设置，则需要在构建SQL语句时考虑它们。例如，如果日期需要为dd.mm.yyyy或者需要为mm / dd / yyyy，则转换将需要不同的参数。我相信在客户端进行转换和连接会更容易。



错误6002是什么。如果你在客户端进行格式化，你可以可能直接从基表中获取数据。在这种情况下，如果表中有有效的主键，则不会再出现错误。



如果你想使用视图，那么你有一些可能性

- 消息可能是一个警告，所以既然你不打算更新视图，你可以忽略警告

- 添加生成视图中的唯一值，并将其定义为模型中的键。生成该值的一种方法是使用 ROW_NUMBER（Transact-SQL ） [ ^ ]

为了做到这一点，您需要告诉SQL您希望日期的格式：SQL Server CONVERT（）函数 [ ^ ] - 在两个日期使用它将它们更改为格式化的NVARCHAR字符串，它将起作用。

格里夫再次击败我:)

我同意， CONVERT（varchar，DateFrom）应该有效，这是一个在线示例： SQL小提琴 [ ^ ]

I want to create view in SQL server and show start date and end date as a single column in this view how it is possible, I tried the code given below and give error "date and varchar are incompatible in the add operator"
My second question is this that in view primery key use

What I have tried:

CREATE VIEW [V_SalesReportLocked] AS
SELECT SRLID, DateFrom+' To'+ DateTo
FROM Reports.SalesReportLocked
WHERE IsActive = 1 and IsClosed=0

解决方案

As others have mentioned when you convert the data into varchar you can concatenate the columns.

However, I wouldn't suggest formatting data this way in the SQL query. Based on your primary key error message you're using EF. If that is correct personally I would prefer to bring the columns as datetime fields into the model and then add calculated fields into the entity to show the dates in desired format. In other words the formatting would be done at client side.

The reason for this is that SQL is not designed for formatting the data, the main usage is data manipulation and retrieval. Another thing is that if the formatting depends on the client side setting, you need to take them into account when building the SQL statement. For example the conversion would require different parameters if the date needs to be dd.mm.yyyy or if it needs to be mm/dd/yyyy. I believe that doing the conversion and concatenation on client side would be easier.

What comes to the error 6002. If you do the formatting on the client side, you could possibly fetch the data directly from the base table. In such case you wouldn't get the error any more in case there is a valid primary key in the table.

If you want to use the view, then you have a few possibilities
- the message is probably a warning, so since you're not going to update the view anyway, you can ignore the warning
- add a generated unique value into the view and define that as a key in the model. One way to generate the value is to use ROW_NUMBER (Transact-SQL)[^]

In order to do that, you need to tell SQL what format you want your dates in: SQL Server CONVERT() Function[^] - use that on both dates to change them to formatted NVARCHAR strings, and it will work.

The Griff beat me again :)
I agree, CONVERT(varchar, DateFrom) should work, here is an online example: SQL Fiddle[^]

这篇关于开始日期和结束日期在SQL Server视图中显示为单个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！