Weeknumber and year in reports

Posted on 2012-08-21
Medium Priority
Last Modified: 2012-09-05
Dear experts,

I have an asp-script generating the present weeknumber for a formular.
Into the database I write 2012-34 which is the year and week we are in now.

My script is this:

Dim wk, intStart, intEnd

wk = DatePart("ww", date())-1
intStart = (wk * 7) +2
intEnd = intStart + 6
<input type="hidden" name="weeknumber" id="weeknumber" value="<%= Year(Now) & "-" & DatePart("ww", Now()) %>" />

Open in new window

But I also have an archieve with reports from the last half year.
I thought the easiest way to make this would be to select them in the database, but my problem is that the weeknumber would be zero if I say:

DatePart("ww", Now()) - 36

Open in new window

So if the week number is above the present weeknumber, my output would be 2011-0, and not 2011-50. Is there a way to make sure I can track the reports from the last 26 weeks correctly?

If there is a better way to write the script, it is ok!

Thanks in advance!
Question by:DanishCoder
  • 3
  • 3
LVL 54

Expert Comment

by:Scott Fell, EE MVE
ID: 38316971
See if this helps with your problem.   If you use the other options for date part you don't have to do the subtraction.   In the code below I am making sure the first day of the week is a sunday ",1" and starting on the first week with 4 days ",2".  This way there will always be weeks 1 - 52.   If you choose to start the week 1 where there is a full week, then you will end up with some years using week53.

Where I have "NewDate" you can simply use your past date.

startdate= #12/20/2011#
for x = 1 to 800
	response.write NewDate&" = "& DatePart("ww", NewDate,1,2)&"<br>"

Open in new window


Author Comment

ID: 38317250

These are reports sent in every week from a person with a login - there is no need for all days and which week they belong to. Therefore, 201x-xx is the ideal solution, but maybe I can't make an archive that way?

LVL 54

Expert Comment

by:Scott Fell, EE MVE
ID: 38317311
The other dates were just for show.  I guess I do not understand what you are doing or what you mean by an archive?  

If the data in your database has a valid date, you can simply use replace the "newdate" with a field from your db.

DatePart("ww", rs("date"),1,2)

It sounds like I am missing something though.  

Into the database I write 2012-34 which is the year and week
Are you storing the actual date or just the year-week?

But I also have an archieve with reports from the last half year.
I thought the easiest way to make this would be to select them in the database, but my problem is that the weeknumber would be zero if I say:
This is where I am lost with your question.  What is the archive?  Is this a table in  your database? Does it have the regular date? or just the date-week?

If you are using date week, I do think it would be a good idea to use the method where you look for the week with the first 4 days and that way you can always compare week 1 with week 1 although week 1 or 52 may not always be a full week.  Or if you prefer to use full weeks, then some years you will have 53 weeks.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 38331443

I have a formular that will be sent by email to the main leader and stored in the database for later review. Now I need an archive to make sure that they have something to review.

Today, I store it as 2012-34 (year-week), but sure, I can store it by date, I just don't do that, because dates in ASP is not always easy to work with.

The formular stored in the database could be sent in on 2011-23, 2011-45, 2012-15, 2012-28...you name it. But I need it to go back 6 months from today's week, so if it is sent in at 2011-45, I should be able to see 7 reports from 2011 and 19 reports from 2012 (or all reports from week 45 to week 2012-19, as their could be some reports not filled out (the system automatically find today's week and dates)):

End Week    Today's Week (could be)
2011-31	     2012-5
2011-32	     2012-6
2011-33	     2012-7
2011-34	     2012-8
2011-35	     2012-9
2011-36	     2012-10
2011-37	     2012-11
2011-38	     2012-12
2011-39	     2012-13
2011-40	     2012-14
2011-41	     2012-15
2011-42	     2012-16
2011-43	     2012-17
2011-44	     2012-18
2011-45	     2012-19
2011-46	     2012-20
2011-47	     2012-21
2011-48	     2012-22
2011-49	     2012-23
2011-50	     2012-24
2011-51	     2012-25
2011-52	     2012-26
2012-01	     2012-27
2012-02	     2012-28
2012-03	     2012-29
2012-04	     2012-30
2012-05	     2012-31
2012-06	     2012-32
2012-07	     2012-33
2012-08	     2012-34

Open in new window

I hope this makes more sense.
There are 26 weeks among "Today's week" and "End week".

LVL 54

Accepted Solution

Scott Fell,  EE MVE earned 2000 total points
ID: 38332339
I think I am more confused....  I'm not sure why you see vbscript dates hard to work with.  I would figure out that issue and use the built in date functions to add and subtract dates. In the end, that will be much easier.

 You didn't confirm which option to use for the starting week and I am using first 4 days in the year as week 1 to make an even 52 weeks.

I have 2 functions.  One to convert year-week to a date and the other to add or subtract the number of week then put it back to year-week.

If it is off, you may just need to adjust which option is the the start of week 1.

 Enter in your year-week and weeks to or from the date
response.write reportDate("2012-34",-36)

function	reportDate(YearWeek,Weeks)

	NextDate = dateadd("ww", Weeks, StartDate)

        'response.write StartDate&" "&NextDate&" "

	reportDate=year(NextDate)&"-"&datepart("ww",NextDate, 1, 2)

end function

function getStartDate(YearWeek)
	'Parse year and week from 2012-13
	parseWeek= trim(right(YearWeek, len(YearWeek) -  5))
	' Convert input to a date
	YearBegin = "1/1/" & parseYear
	StartingDate = DateAdd("ww", parseWeek, YearBegin)
	EndingDate = DateAdd("d", 6, StartingDate)

end function

Open in new window


Author Closing Comment

ID: 38370187
Sorry for the delay (and confusion) I made up.
Anyway, that was exactly what I was looking for.

So now I have made it a little more dynamic:
ReportWeek1	= Year(Now) & "-" & DatePart("ww", Now())

'function 1
'function 2

Open in new window

so my SQL looks like this:
"SELECT [DateSent], [ReportWeek], [ReportWeekDates] FROM reports WHERE UserID = " & session("id") & " AND ReportWeek BETWEEN " & reportDate("" & ReportWeek1 & "",-26) & " AND " & ReportWeek1 & " ORDER BY ReportID DESC"

Open in new window

Which will give:
SELECT [DateSent], [ReportWeek], [ReportWeekDates] FROM reports WHERE UserID = 71 AND ReportWeek BETWEEN 2012-11 AND 2012-36 ORDER BY ReportID DESC

Open in new window

Thanks again, and have a great weekend...


Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question