在SQL Server中显示为日期范围 [英] dispaly as date range in sql server

问题描述

我的表tblUsers具有示例值

用户ID离开日期
1 01 \ 03 \ 2011
1 02 \ 03 \ 2011
1 03 \ 03 \ 2011
1 06 \ 03 \ 2011
1 10 \ 03 \ 2011
1 11 \ 03 \ 2011

我想用逗号分隔符将结果集检索为日期范围，如下面的结构.如果连续三个日期出现，则将其分组，否则将其设为单个.

结果为

userID离开日期
1 01 \ 03 \ 2011-03 \ 03 \ 2011，
06 \ 03 \ 2011.10 \ 03 \ 2011-11 \ 03 \ 2011

请给我这个想法吗?

Hi,

I have table tblUsers with sample value

UserID Leavedate
1 01\03\2011
1 02\03\2011
1 03\03\2011
1 06\03\2011
1 10\03\2011
1 11\03\2011

I want to retrieve the result set as date range like below structure with comma separtor.If three consecutive dates comes then group it otherwise make it as single .

Excepted Result would be

userID Leavedate
1 01\03\2011 - 03\03\2011,
06\03\2011.10\03\2011 - 11\03\2011

Please give me idea on this ?

推荐答案

这实际上是PITA要做的.第一个问题是:您将返回哪种数据类型?
您必须返回一个字符串:因为您可能要返回范围或逗号分隔的值.
但是，由于没有返回类型来指示结果是单个还是多个结果，因此必须对结果进行后处理才能确定实际的日期.

Mika是正确的，您必须将其创建为存储过程才能返回您所要的内容，但是我建议一种更好的方法可能是重新考虑数据库结构或您对数据库的处理方式数据-因为返回的行几乎需要对要使用它的任何应用程序进行重新处理.

您想要达到什么目的，需要它?

That''s actually a PITA to do. The first question is: what data type are you going to return?
You have to return a string: because you are potentially returning a range, or comma separated values.
But because you don''t have a return type to indicate if the result is a single or multiple result, you have to post-process your results to work out what dates you actually have.

Mika is correct that you would have to create this as a stored procedure in order to return what you have asked for, but I would suggest that a better way might be to re-think either your database structure, or what you are doing with the data - as the returned row will need re-processing for almost any application you want to use it for.

What are you trying to achieve, that you need this?

也许最简单的方法是创建一个小的过程并处理过程中的串联.然后从该过程返回结果集.

Perhaps the simplest way to do this is to create a small procedure and handle the concatenation in the procedure. Then return the result set from the procedure.

这篇关于在SQL Server中显示为日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！