Giving only the week how can I get first date and last date of that week? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

Giving only the week how can I get first date and last date of that week?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-25 : 09:13:17

Carlos Martinho writes "Giving only the week nr (e.g. week 3) how can I get the date of the first day of the week and the date of the last day in the same week?

I want to produce a result like this:
Week: 3 (from 1/13/2001 to 1/19/2001)

If you could also aswer the following I would be very appreciated:
DECLARE @FromDate datetime
DECLARE @ToDate datetime
SET @FromDate = '1/1/02 9:02:30'
SET @ToDate = '1/14/02 18:31:25'

Giving the above two dates, how can I get the elapsed time between those dates in the following format?

Elapsed time: X days, X hours, X minutes and X seconds

Thank you
Carlos"

lfmn
Posting Yak Master

141 Posts

Posted - 2002-01-25 : 13:47:02

Although there may be a better way, we set up a table where I work which has fiscal year, beginning date, ending date and week number which you could use for the first part of your question. You can use the select DATEPART(wk, getdate())to get number of the week, but I don't know of any function where you would supply the number of the week and get the dates.

for your second question, you could use DATEDIFF(datepart, startdate, enddate) to get the difference in seconds and then use the modulo function (dividend % divisor) to break the seconds into days, hours, etc. If you are using SQL Server 2000, you could write a function to handle this, otherwise, I would recommend a stored procedure.

BOL has excellent coverage of these functions

cursors are like hammers - sometimes you have to use them, but watch your thumb!

DGMelkin
Starting Member

24 Posts

Posted - 2002-01-25 : 18:14:18

I think this might be the solution to the first part of the problem, with the first day being Monday and the last day being Sunday (at least, that's what it looks like to me.):

DECLARE @week int
DECLARE @startdate datetime

SELECT @week = 3
SELECT @startdate = DATEADD(dd, @week * 7, '1/1/2002')

IF DatePart(dw, @startdate) <> 1
SELECT @startdate = DATEADD(dd, DatePart(dw, @startdate) - (DatePart(dw, @startdate) + 1), '1/1/2002')

SELECT @startdate as WeekStart, DATEADD(dd, 6, @startdate) as WeekEnd

Hope this works for you.

-D

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-01-26 : 10:44:05

This is along the lines of the solution offered by lfmn. If you have a tally table with all the dates for a year you can group by the datepart and use min() and max() functions to get you dates.


--create the tally table
use tempdb
go
drop table YearTally
go
create table YearTally (CalDate smalldatetime)
go
set nocount on 
go
declare @nextday integer
declare @insertdate smalldatetime
select @nextday = 0
select @insertdate = '1/1/2002'
while datepart(yy, (@insertdate + @nextday)) = 2002
begin
  insert into YearTally values (dateadd(dd, @nextday, @insertdate))
  set @nextday = @nextday + 1
end
go 
set nocount off


-- the actual query
declare @week integer
select @week = 3

select datepart(wk, caldate), min(caldate), max(caldate)
from yeartally
where datepart(wk, caldate) = @week
group by datepart(wk, caldate)

hth,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources