LINQ:按日期排序时,其作为文本存储 [英] Linq: Sort by Date when its stored as text
问题描述
我需要按日期进行排序,但日期存储在数据库中的文字。我使用LINQ到实体执行查询。
的
数据库被设计它的方式是不可行的列改变为日期列,因为许多不同的数据类型是在该列。有一个名为键入
所以我知道什么类型的特定行。
您可以计算列添加到将这些字符串转换为日期表时,你的鉴别有我只是用一个特定的值(这里是'日期'
)。
ALTER TABLE富
ADD trueDate AS
CASE
当类型= 日期然后将其转换(日期,'mixedColumn',101)
ELSE NULL
端
坚持
如果你有时间的信息,那么日期
应日期时间
在 CONVERT()
功能。
此外, 101
是一个代码风格的说明 MM / DD / YYYY
期望的格式。如果您有什么不同的,参照本: http://msdn.microsoft.com /en-us/library/ms187928.aspx ,但请记住,如果你使用低于100的表达风格将被视为非确定性的,你不能让你的计算列 PERSISTED
,这样的转换就与每个查询实时进行(你不希望出现这种情况)。
在计算列会自动更新该行值的变化;否则,值依然存在,可查询就像任何其他列。无需触发。
I need to sort by date but the date is stored as text in the database. I am using Linq to entities to perform queries.
The way the database is designed it is not feasible to change the column to a date column because many different data types are in that column. There is a descriminator column named type
so I will know what type a particular row is.
You can add a computed column to the table that will convert those strings to dates when your discriminator has a specific value (here I've just used 'date'
).
ALTER TABLE Foo
ADD trueDate AS
CASE
WHEN type = 'date' THEN CONVERT(date, 'mixedColumn', 101)
ELSE NULL
END
PERSISTED
If you have time information, then date
should be datetime
in the CONVERT()
function.
Also, the 101
is a style code indicating an expected format of MM/dd/yyyy
. If you have something different, refer to this: http://msdn.microsoft.com/en-us/library/ms187928.aspx, but keep in mind that if you use a style below 100 your expression will be considered non-deterministic and you cannot make your computed column PERSISTED
, so the conversions will be done on the fly with each query (you don't want that).
The computed column will update itself when the row values change; otherwise the values are persisted and queryable just like in any other column. No triggers required.
这篇关于LINQ:按日期排序时,其作为文本存储的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!