Finding Week on date

  • 12 years ago

    Hello all,


    Im having this table called expenses with field expensesdate and amount
    My question is, is it possible for me to get the data per week base on expenses
    date. For example week 1, week 2 and so on. Im so confuse on how to group the date into week.


    Thanks

  • 12 years ago

    You will probably have to alter this code a bit depending on exactly what period your dates span, to take into account different years and so forth, but here is the general principle.

    Code:
       Private Function GetWeekNumber(ByVal expenseDate As Date) As Integer
           'The first day of the year.
           Dim startDate As Date = New Date(Date.Today.Year, 1, 1)


           'Get the time difference between date of the expense and the start of the year.
           Dim timeDifference As TimeSpan = expenseDate.Date.Subtract(startDate)


           'Get the time difference in days.
           Dim daysDifference As Integer = timeDifference.Days


           'Add 1, divide by 7 and round up to get the week number.
           Dim weekNumber As Integer = Math.Ceiling((daysDifference + 1) / 7)


           Return weekNumber
       End Function

    I have detailed every step here but you could actually combine all this into a single line of code if you wanted to.  Obviously, expand it as much as you need to to be able to understand it.  A single line might look rather confusing.Code:
       Private Function GetWeekNumber(ByVal expenseDate As Date) As Integer
           Return Math.Ceiling((expenseDate.Date.Subtract(New Date(Date.Today.Year, 1, 1)).Days + 1) / 7)
       End Function

  • 12 years ago

    Thanks jmcilhinney, ur code is very helpful. Its a bit confusing but thats okey as long I can get the week :P.


    Thanks

  • 12 years ago
    In actual fact I made it more complex than was necessary.  I overlooked something that I knew existed but had kind of forgotten about: the DateTime.DayOfYear property.  Using this property, my entire function reduces to:Code:
       Private Function GetWeekNumber(ByVal expenseDate As Date) As Integer
           Return Math.Ceiling(expenseDate.DayOfYear / 7)
       End Function
    A couple of points to note here:
    1. This assumes that you want the week number based on calender year.  If you want to use financial year (July to June in Australia, I assume the same elsewhere) you will need to adjust accordingly.
    2. If the first day of the year was, for example, a Thursday, the week numbers are calculated based on a week being Thursday to Wednesday.  If you want Sunday to Saturday or Monday to Sunday you will need to adjust accordingly.

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“C++ : Where friends have access to your private members.” - Gavin Russell Baker