sql查询使用日期名称，星期开始日期和时间显示日期。周结束日期。 [英] sql query to display date using day name, week start date & week end date.

问题描述

sql查询使用日期名称，星期开始日期和时间显示日期周结束日期。



我的数据如下所示

Hi,

sql query to display date using day name, week start date & week end date.

my data looks like below

DAYName	hours	WeekBeginDt	WeekEndDt
MonHrs	8	2010-01-18 	2010-01-24 
TueHrs	8	2010-01-18 	2010-01-24 
WedHrs	8	2010-01-18 	2010-01-24 
ThuHrs	8	2010-01-18 	2010-01-24 
FriHrs	8	2010-01-18 	2010-01-24 
SatHrs	8	2010-01-18 	2010-01-24

i想要下面的数据

i want data like below

DAYName	hours  date 	WeekBeginDt	         WeekEndDt
MonHrs	8    2010-01-18        2010-01-18 	2010-01-24 
TueHrs	8    2010-01-19	        2010-01-18 	2010-01-24 
WedHrs	8    2010-01-20	        2010-01-18 	2010-01-24 
ThuHrs	8    2010-01-20	        2010-01-18 	2010-01-24 
FriHrs	8    2010-01-21	        2010-01-18 	2010-01-24 
SatHrs	8    2010-01-22	        2010-01-18 	2010-01-24 

谢谢，

Thanks,

推荐答案

试试这个：

Try this:

DECLARE @tmp TABLE([DAYName] VARCHAR(30), hours INT, WeekBeginDt DATETIME, WeekEndDt DATETIME)

INSERT INTO @tmp ([DAYName], hours, WeekBeginDt, WeekEndDt)
SELECT 'MonHrs', 8, '2010-01-18', '2010-01-24'
UNION ALL SELECT 'TueHrs', 8, '2010-01-18', '2010-01-24'
UNION ALL SELECT 'WedHrs', 8, '2010-01-18', '2010-01-24' 
UNION ALL SELECT 'ThuHrs', 8, '2010-01-18', '2010-01-24' 
UNION ALL SELECT 'FriHrs', 8, '2010-01-18', '2010-01-24' 
UNION ALL SELECT 'SatHrs', 8, '2010-01-18', '2010-01-24'

;WITH MyDates AS
(
        --initial values
	SELECT [DAYName], hours, WeekBeginDt AS CurrDate, WeekBeginDt, WeekEndDt
	FROM @tmp
	UNION ALL
        --recursive part
	SELECT [DAYName], hours, DATEADD(dd,1, CurrDate) AS CurrDate, WeekBeginDt, WeekEndDt
	FROM MyDates
	WHERE DATEADD(dd,1, CurrDate)<=WeekEndDt
)
SELECT *
FROM MyDates
WHERE LEFT(DATENAME(dw, CurrDate),3) + 'Hrs' = [DAYName]
ORDER BY CurrDate

在上面的示例中，我使用公用表表达式（CTE） 。

这篇关于sql查询使用日期名称，星期开始日期和时间显示日期。周结束日期。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！