Fetching data for a Calander Week

Results 1 to 3 of 3

Thread: Fetching data for a Calander Week

  1. #1
    Sadik Mohammed Guest

    Default Fetching data for a Calander Week

    This probabily is not the right four to ask this question, still ...<BR><BR>Is it possible to select data on the basis on weeks of a year. Like how can i fetch name of all students who were enrolled in 15th week of a year.<BR><BR>Sadik Mohammed

  2. #2
    Join Date
    Dec 1969

    Default Good question...

    Part of the problem here is defining what you mean by the "15th week of the year." Do you mean the 15th week, starting with the first *FULL WEEK* of the year? Or do you mean the 15th week, where week 1 is the week containing January 1st, no matter what day of the week it falls on?<BR><BR>Strangely enough, the VBS function "DateDiff" allows you to specify either of those--and two other options as well!<BR><BR>But "DateAdd" does *not* do so. And "DateAdd" is the function you want here.<BR><BR>Specifically:<BR><BR>&#060;%<BR>week1 5 = DateAdd( "ww", 15, DateSerial( 2000, 1, 1 ) )<BR>%&#062;<BR><BR>Except that will get you the same day of the week as January 1 of the given year, not the first day of that week.<BR><BR>So... Let&#039s play with this a bit.<BR><BR>How about this? In this code, I assume that the first week of the year is the first *FULL* week--using Monday through Friday only!--that contains January 1st. You&#039ll have to adjust if the assumptions you make are different.<BR><BR>&#060;%<BR>startDate = DateSerial( 2000, 1, 1 ) &#039 or whatever year you will use<BR>startday = WeekDay( startDate ) &#039 which day of week is Jan 1?<BR>If startday = 1 Then <BR>&nbsp; &nbsp; &#039 Jan 1 is Sunday, so make startDate be Jan 2, Monday<BR>&nbsp; &nbsp; startDate = DateSerial( 2000, 1, 2 )<BR>ElseIf startday &#062; 2 Then<BR>&nbsp; &nbsp; &#039 if it is not Sunday or Monday, then...<BR>&nbsp; &nbsp; &#039 ...this is not first FULL week of the year...<BR>&nbsp; &nbsp; &#039 Move forward...<BR>&nbsp; &nbsp; startDate = DateSerial( 2000, 1, 10-startday ) &#039 tricky, but works!<BR>End If<BR>&#039 you can validate it at this point:<BR>Response.Write "First day of first full week is " & FormatDateTime( startDate, vbLongDate )<BR>&#039<BR>&#039 now it is *easy* to go to 15th week:<BR>&#039<BR>weekNstart = DateAdd( "ww", weekNum-1, startDate )<BR>weekNend = DateAdd( "d", 4, startDate )<BR>&#039<BR>Response.Write "Start of week " & weekNum & " is " & FormatDateTime( weekNstart, vbLongDate )<BR>Response.Write "End of week " & weekNum & " is " & FormatDateTime( weekNend, vbLongDate )<BR>%&#062;<BR><BR>Hokay?<BR><BR>So now, to use it in a query:<BR><BR>&#060;%<BR>SQL = "SELECT * FROM students WHERE enrolledStart &#060;= #" & weekNend & "#" _<BR>&nbsp; &nbsp; &nbsp; &nbsp; & " AND enrolledEnd &#062;= #" & weekNstart & "#"<BR>%&#062;<BR><BR>Be careful! If the enrolledStart/enrolledEnd fields in your DB are actually Date/Time values instead of just Dates alone, then the &#060;= test vs. weekNend won&#039t work right! That&#039s because you will be comparing something like "3/22/2000 15:13:20" vs. "3/22/2000 0:00:00" (any date with no time given *assumes* 0:00:00 time!) and, of course, that won&#039t give the right answer!<BR><BR>The simple thing to do, which works whether time is used or not, is <BR><BR>&#060;%<BR>weekNend = DateAdd( "d", 5, startDate ) &#039 SATURDAY! Not Friday!<BR>%&#062;<BR><BR>And then<BR><BR>&#060;%<BR>SQL = "SELECT * FROM students WHERE enrolledStart &#060; #" & weekNend & "#" _<BR>&nbsp; &nbsp; &nbsp; &nbsp; & " AND enrolledEnd &#062;= #" & weekNstart & "#"<BR>%&#062;<BR><BR>See that? Now you use &#060; instead of &#060;= and it works no matter whether time is there or not.<BR><BR>Whew. Long answer, but it&#039s not as easy a question as you may have thought!<BR><BR><BR>

  3. #3
    Join Date
    Dec 1969
    Los Angeles, CA

    Default WOW...does Billy have patience or........

    ...does Billy have patience....Man looking at that post i dread to think what some people would do here without him. <BR><BR>I for one WOULD never even attempted to give the answer the way he did. I would have outlined a few functions and then thrown the guy (oops person) into the lion pit.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts