Need week Time range starting each Monday

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need week Time range starting each Monday

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-12-24 : 08:59:20

I have a table :

select OPEN_TIME from dbo.ORDERS

I need to display all orders like this:

OPEN_TIME, WEEK_DATE_RANGE, WEEKNO

2013-08-05 08:32:02.000 08/05/2013 - 08/11/2013

I am using currently this SELECT statement which will return return different week range (starting Sunday) and I need Monday a s the start of the week.

select OPEN_TIME,convert(varchar,convert(date,(DATEADD(day, -1 ,DATEADD(wk, DATEDIFF(wk,0,OPEN_TIME), 0)))),101) + ' - ' + convert(varchar,convert(date,(DATEADD(day, +5 ,DATEADD(wk, DATEDIFF(wk,0,OPEN_TIME), 0)))),101) as OPEN_TIMEDateRange from dbo.ORDERS

Thank you

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-24 : 12:10:38

Does this help?

DECLARE @Orders TABLE(OPEN_TIME DATETIME)

INSERT @Orders VALUES
('2013-08-05 08:32:02.000 '),
('2012-08-05 08:32:02.000 '),
('2013-09-05 08:32:02.000 '),
('2013-08-17 08:32:02.000 '),
('2013-07-13 08:32:02.000 '),
('2013-08-06 08:32:02.000 '),
('2013-08-04 08:32:02.000 '),
('2013-08-03 08:32:02.000 ')


SELECT
	OPEN_TIME,
	DATEADD(WEEK, DATEDIFF(WEEK, '19000101', OPEN_TIME), '19000101') AS StartDate,
	DATEADD(DAY, -1, DATEADD(WEEK, DATEDIFF(WEEK, '19000101', OPEN_TIME) + 1, '19000101')) AS EndDate
FROM
	@Orders

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-12-24 : 12:30:18

This will handle week dates, regardless of any language and/or date settings:



SELECT
    OPEN_TIME,
    CONVERT(varchar(10), DATEADD(DAY, DATEDIFF(DAY, 0, OPEN_TIME) / 7 * 7, 0), 101) + ' - ' +
    CONVERT(varchar(10), DATEADD(DAY, DATEDIFF(DAY, 0, OPEN_TIME) / 7 * 7 + 7, 0), 101) AS WEEK_DATE_RANGE
FROM @Orders

Week# is trickier. Would need to know how you determine week#1 in a year.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-24 : 12:30:53

quote:

Originally posted by Lamprey

Does this help?

DECLARE @Orders TABLE(OPEN_TIME DATETIME)

INSERT @Orders VALUES
('2013-08-05 08:32:02.000 '),
('2012-08-05 08:32:02.000 '),
('2013-09-05 08:32:02.000 '),
('2013-08-17 08:32:02.000 '),
('2013-07-13 08:32:02.000 '),
('2013-08-06 08:32:02.000 '),
('2013-08-04 08:32:02.000 '),
('2013-08-03 08:32:02.000 ')


SELECT
	OPEN_TIME,
	DATEADD(WEEK, DATEDIFF(WEEK, '19000101', OPEN_TIME), '19000101') AS StartDate,
	DATEADD(DAY, -1, DATEADD(WEEK, DATEDIFF(WEEK, '19000101', OPEN_TIME) + 1, '19000101')) AS EndDate
FROM
	@Orders

this is not working correct for Sundays. As per Op he wants week to be considered from Monday to Sunday

see the difference highlighted below


SET DATEFIRST 7
GO
DECLARE @Orders TABLE(OPEN_TIME DATETIME)

INSERT @Orders VALUES
('2013-08-05 08:32:02.000 '),
('2012-08-05 08:32:02.000 '),
('2013-09-05 08:32:02.000 '),
('2013-08-17 08:32:02.000 '),
('2013-07-13 08:32:02.000 '),
('2013-08-06 08:32:02.000 '),
('2013-08-04 08:32:02.000 '),
('2013-08-03 08:32:02.000 ')


SELECT
	OPEN_TIME,
	DATEADD(WEEK, DATEDIFF(WEEK, '19000101', OPEN_TIME), '19000101') AS StartDate,
	DATEADD(DAY, -1, DATEADD(WEEK, DATEDIFF(WEEK, '19000101', OPEN_TIME) + 1, '19000101')) AS EndDate
FROM
	@Orders

select OPEN_TIME,DATEADD(dd,DATEDIFF(dd,0,OPEN_TIME)/7 *7,0) AS Start,
DATEADD(dd,((DATEDIFF(dd,0,OPEN_TIME)/7)+1)*7,0)-1 AS [End]
FROM
	@Orders


output
1st query
---------------------------------------------------------
OPEN_TIME	StartDate	EndDate
----------------------------------------------------------
2013-08-05 08:32:02.000	2013-08-05 00:00:00.000	2013-08-11 00:00:00.000
2012-08-05 08:32:02.000	2012-08-06 00:00:00.000	2012-08-12 00:00:00.000
2013-09-05 08:32:02.000	2013-09-02 00:00:00.000	2013-09-08 00:00:00.000
2013-08-17 08:32:02.000	2013-08-12 00:00:00.000	2013-08-18 00:00:00.000
2013-07-13 08:32:02.000	2013-07-08 00:00:00.000	2013-07-14 00:00:00.000
2013-08-06 08:32:02.000	2013-08-05 00:00:00.000	2013-08-11 00:00:00.000
2013-08-04 08:32:02.000	2013-08-05 00:00:00.000	2013-08-11 00:00:00.000
2013-08-03 08:32:02.000	2013-07-29 00:00:00.000	2013-08-04 00:00:00.000



2ndquery
--------------------------------------------------------
OPEN_TIME	Start	End
---------------------------------------------------------
2013-08-05 08:32:02.000	2013-08-05 00:00:00.000	2013-08-11 00:00:00.000
2012-08-05 08:32:02.000	2012-07-30 00:00:00.000	2012-08-05 00:00:00.000
2013-09-05 08:32:02.000	2013-09-02 00:00:00.000	2013-09-08 00:00:00.000
2013-08-17 08:32:02.000	2013-08-12 00:00:00.000	2013-08-18 00:00:00.000
2013-07-13 08:32:02.000	2013-07-08 00:00:00.000	2013-07-14 00:00:00.000
2013-08-06 08:32:02.000	2013-08-05 00:00:00.000	2013-08-11 00:00:00.000
2013-08-04 08:32:02.000	2013-07-29 00:00:00.000	2013-08-04 00:00:00.000
2013-08-03 08:32:02.000	2013-07-29 00:00:00.000	2013-08-04 00:00:00.000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-24 : 12:38:39

Argh, yeah, good catch. I spaced on default day of the week.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-12-24 : 12:58:23

SET DATEFIRST could of course invalidate other parts of the code in the query. Therefore, I think it's best to use a datefirst-neutral method, such as the one I posted.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-24 : 13:03:44

quote:
Originally posted by ScottPletcher

SET DATEFIRST could of course invalidate other parts of the code in the query. Therefore, I think it's best to use a datefirst-neutral method, such as the one I posted.

Agreed.

For posterity, here is how you can use the built in date functions along with the datefirst command, to get the proper results:

SET DATEFIRST 1

SELECT
	OPEN_TIME,
	DATEADD(WEEK, DATEDIFF(WEEK, DATEADD(DAY, -@@DATEFIRST, '19000101'), DATEADD(DAY, -@@DATEFIRST, OPEN_TIME)), '19000101') AS StartDate,
	DATEADD(DAY, -1, DATEADD(WEEK, DATEDIFF(WEEK, DATEADD(DAY, -@@DATEFIRST, '19000101'), DATEADD(DAY, -@@DATEFIRST, OPEN_TIME)) + 1, '19000101')) AS EndDate
FROM
	@Orders

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-24 : 13:30:24

quote:
Originally posted by ScottPletcher

SET DATEFIRST could of course invalidate other parts of the code in the query. Therefore, I think it's best to use a datefirst-neutral method, such as the one I posted.

yes of course
Thats why i always tend to use logic as below

http://visakhm.blogspot.in/2012/08/creating-server-independent-day.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-25 : 05:20:15

Shorter.

SELECT	OPEN_TIME,
	DATEADD(DAY, DATEDIFF(DAY, '19000101', OPEN_TIME) / 7 * 7, '19000101') AS StartDate,
	DATEADD(DAY, DATEDIFF(DAY, '19000101', OPEN_TIME) / 7 * 7, '19000107') AS EndDate
FROM	@Orders;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-12-25 : 21:15:50

quote:
Originally posted by SwePeso

Shorter.
SELECT OPEN_TIME,
DATEADD(DAY, DATEDIFF(DAY, '19000101', OPEN_TIME) / 7 * 7, '19000101') AS StartDate,
DATEADD(DAY, DATEDIFF(DAY, '19000101', OPEN_TIME) / 7 * 7, '19000107') AS EndDate
FROM @Orders;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Shorter? It's the identical calc, juist not converted to the format the OP requested.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-26 : 06:07:31

One less operation (+ 7).

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-12-26 : 10:11:13