Given a date, how to find the beginning date and ending date of that week

[Matt]

My ASP page allows the user select the date, and it will display the report
during that week of that date.

My question is when the program query database, I need to know the beginning
date and ending date of that week.

Any ideas??

 

[Rob Meade]

...

My question is when the program query database, I need to know the beginning
date and ending date of that week.

<%
' strDate = RS("Date")
strDate = Date()

strWeekdayName = LCase(WeekdayName(weekday(strDate)))

Dim myDaysArray(7)

myDaysArray(0) = "monday"
myDaysArray(1) = "tuesday"
myDaysArray(2) = "wednesday"
myDaysArray(3) = "thursday"
myDaysArray(4) = "friday"
myDaysArray(5) = "saturday"
myDaysArray(6) = "sunday"

For intLoop = 0 To UBound(myDaysArray)

If strWeekdayName = myDaysArray(intLoop) Then

If intLoop = 0 Then

strStartOfWeekDate = strDate
strEndOfWeekDate = DateAdd("d", 6, strDate)

ElseIf intLoop = 6 Then

strStartOfWeekDate = DateAdd("d", -6, strDate)
strEndOfWeekDate = strDate

Else

strStartOfWeekDate = DateAdd("d", -intLoop , strDate)
strEndOfWeekDate = DateAdd("d", (6-intLoop), strDate)

End If

End If

Next

Response.Write "Current Date : " & strDate & "<br>"
Response.Write "Start Of Week Date : " & strStartOfWeekDate & "<br>"
Response.Write "End Of Week Date : " & strEndOfWeekDate & "<br>"
%>

If your 'week' doesn't start on a Monday, change the values in the array, ie
:

tueday
wednesday
thursday
friday
saturday
sunday
monday

Hope this helps, oh and if you run it as it is at the moment it should work
fine, commented line at the top for your date from the database etc, then
just remove the response.writes at the bottom.

Regards

Rob

 

[Bob Barrows]

My ASP page allows the user select the date, and it will display the
report during that week of that date.

My question is when the program query database, I need to know the
beginning date and ending date of that week.

Any ideas??

Don't you think the type and version of database you are using might be
relevant information (it is)?

Do you want to calculate these dates in the vbscript code? or in the
database query?

 

[Rob Meade]

...

Don't you think the type and version of database you are using might be
relevant information (it is)?

Do you want to calculate these dates in the vbscript code? or in the
database query?

Arrghh!! You're going to post up a 2 line SQL statement now Bob aren't you
that will do exactly what Matt wants and will make me look bad ;o)

Rob

 

[Bob Barrows]

...


Arrghh!! You're going to post up a 2 line SQL statement now Bob
aren't you that will do exactly what Matt wants and will make me look
bad ;o)

Rob

It depends on what his database is ...

Bob

 

[Rob Meade]

...

It depends on what his database is ...

I know some of the Weekday stuff I've done in the code above can be done in
the SQL statement in SQL Server, any better suggestions for the ASP version
which I did above?

I dont often put forward a code block for people in the group as I'm often
concerned I'll do it in about 100 lines when 10 would have worked

Rob

 

[Bob Barrows]

...


I know some of the Weekday stuff I've done in the code above can be
done in the SQL statement in SQL Server, any better suggestions for
the ASP version which I did above?

I dont often put forward a code block for people in the group as I'm
often concerned I'll do it in about 100 lines when 10 would have
worked

Rob

Are you sure you want me to answer that?

dim dToday, dWkStartDate, dWkEndDate
dToday = Date()
'assuming Sunday is first day of week:
dWkStartDate = dToday
Do until weekday(dWkStartDate) = vbSunday
dWkStartDate = DateAdd("d",-1,dWkStartDate)
Loop
dWkEndDate = DateAdd("d",6,dWkStartDate)

Bob Barrows


MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Rob Meade]

...

Are you sure you want me to answer that?

dim dToday, dWkStartDate, dWkEndDate
dToday = Date()
'assuming Sunday is first day of week:
dWkStartDate = dToday
Do until weekday(dWkStartDate) = vbSunday
dWkStartDate = DateAdd("d",-1,dWkStartDate)
Loop
dWkEndDate = DateAdd("d",6,dWkStartDate)

grrrrr.....

hehe - as I suspected - all over in half a dozen lines of code...bah...last
time I make a suggestion! )

Rob

 

[dlbjr]

dtmDate = "10/12/03"
dtmStartOfWeek = DateAdd("d",-(DatePart("w",dtmDate) - 1),dtmDate)
dtmEndOfWeek = DateAdd("d",7 - DatePart("w",dtmDate),dtmDate)

-dlbjr

Discerning resolutions for the alms

 

[Rob Meade]

...and now were down to 3 lines of code... D

Any advances on 3....come on - someone must be able to do it ....

Rob

 

[Chris Barber]

Well ... one assignment and two calculations = 3 lines.

But .. if you use colons - multiple statements - then you could do:

dtmDate = "10/12/03":dtmStartOfWeek = DateAdd("d",-(DatePart("w",dtmDate) -
1),dtmDate):dtmEndOfWeek = DateAdd("d",7 - DatePart("w",dtmDate),dtmDate)

and do it all on one line?

My personal usage would become:

dtmDate = "10/12/03"
dtmDateStartDiff = 1 - DatePart("w",dtmDate)
dtmStartOfWeek = DateAdd("d",dtmDateStartDiff,dtmDate)
dtmEndOfWeek = DateAdd("d",7,dtmStartOfWeek)

if only that it seems a little more 'informative' to a developer roaming his
eye over the code trying to figure out why the weeks are starting on
Tuesday. Its the same calculation of course but just expressed in a slightly
different way.

Chris.

...and now were down to 3 lines of code... D

Any advances on 3....come on - someone must be able to do it ....

Rob

 

[Aaron Bertrand [MVP]]

d = "11/01/2003"
response.write dateadd("d", 1 - weekday(d), d) & "<p>"
response.write dateadd("d", 7 - weekday(d), d)