Weeknumber and year in reports

Posted on 2012-08-21
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
    LVL 52

    Expert Comment

    by:Scott Fell, EE MVE
    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


    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 52

    Expert Comment

    by:Scott Fell, EE MVE
    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.

    Author Comment


    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, 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 52

    Accepted Solution

    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

    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

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
    I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    This video discusses moving either the default database or any database to a new volume.

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now