SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need week Time range starting each Monday
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nietzky
Yak Posting Veteran

72 Posts

Posted - 12/24/2013 :  08:59:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/24/2013 :  12:10:38  Show Profile  Reply with Quote
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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
410 Posts

Posted - 12/24/2013 :  12:30:18  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/24/2013 :  12:30:53  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/24/2013 :  12:38:39  Show Profile  Reply with Quote
Argh, yeah, good catch. I spaced on default day of the week.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
410 Posts

Posted - 12/24/2013 :  12:58:23  Show Profile  Reply with Quote
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.

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/24/2013 :  13:03:44  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/24/2013 :  13:30:24  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 12/25/2013 :  05:20:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 12/25/2013 05:57:13
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
410 Posts

Posted - 12/25/2013 :  21:15:50  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 12/26/2013 :  06:07:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
One less operation (+ 7).



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
410 Posts

Posted - 12/26/2013 :  10:11:13  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

One less operation (+ 7).



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



LOL, OK; also less clear what is happening.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 12/28/2013 :  05:17:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Really?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000