displaying date in a period format
i have the following table with values mentioned below.I have a label which show开发者_运维技巧s the date from the table.for the first date it should display as 07/06/2011 and from the second date it should display as below format,any one help me..
"Label:07/07/2011 to 07/13/2011 " ,
ID Date
== ==========
63 07/06/2011
64 07/13/2011
65 07/20/2011
66 07/27/2011
67 08/03/2011
Try this:
WITH qry AS
(
SELECT a.*,
ROW_NUMBER() OVER(ORDER BY Date) rn
FROM MyTable a
)
SELECT a.Id,
CASE
WHEN b.Id IS NULL THEN CONVERT(VARCHAR, a.Date, 101)
ELSE 'Label: ' +
CONVERT(VARCHAR, b.Date + 1, 101) +
' to ' +
CONVERT(VARCHAR, a.Date , 101)
END AS Date
FROM qry a LEFT JOIN qry b
ON a.rn = b.rn+1
Table Setup:
CREATE TABLE MyTable (ID INT, DATE DATETIME)
INSERT INTO MyTable VALUES(63,'07/06/2011');
INSERT INTO MyTable VALUES(64,'07/13/2011');
INSERT INTO MyTable VALUES(65,'07/20/2011');
INSERT INTO MyTable VALUES(66,'07/27/2011');
INSERT INTO MyTable VALUES(67,'08/03/2011');
Results:
Id Date
63 07/06/2011
64 Label: 07/07/2011 to 07/13/2011
65 Label: 07/14/2011 to 07/20/2011
66 Label: 07/21/2011 to 07/27/2011
67 Label: 07/28/2011 to 08/03/2011
Depending on yout day/month format you could try:
SELECT
CONVERT(VARCHAR, [date] , 4)
精彩评论