Date Ranges and vb.net?

I want to be able to filter a gridview on a date range of a week, month and year. How would I go about getting the current weeks values and the same must apply to the month and year.


I am getting the data that I am wanting to view from a sql database and in the table there is a LogDate field which stores the value as a short date string.Date Ranges and vb.net?
I am not quite sure what you're trying to accomplish, but I'm assuming that you want to run a SQL query and return only records where the LogDate value is after midnight for Sunday of this week and before midnight of Saturday of this week. If that's the case, use the following code to determine your startdate and your enddate, and then pass these values into your SQL queries as parameters, like this:





Public Class Form1





Private Sub A() Handles Me.Load


Dim startDay As DateTime


Dim endDay As DateTime


Select Case DateTime.Now.DayOfWeek


Case DayOfWeek.Sunday


startDay = New DateTime(Now.Year, Now.Month, Now.Day)


endDay = New DateTime(Now.Year, Now.Month, Now.Day, 23, 59, 59) + New TimeSpan(6, 0, 0, 0)


Case DayOfWeek.Monday


startDay = New DateTime(Now.Year, Now.Month, Now.Day) - New TimeSpan(1, 0, 0, 0)


endDay = New DateTime(Now.Year, Now.Month, Now.Day, 23, 59, 59) + New TimeSpan(5, 0, 0, 0)


Case DayOfWeek.Tuesday


startDay = New DateTime(Now.Year, Now.Month, Now.Day) - New TimeSpan(2, 0, 0, 0)


endDay = New DateTime(Now.Year, Now.Month, Now.Day, 23, 59, 59) + New TimeSpan(4, 0, 0, 0)


Case DayOfWeek.Wednesday


startDay = New DateTime(Now.Year, Now.Month, Now.Day) - New TimeSpan(3, 0, 0, 0)


endDay = New DateTime(Now.Year, Now.Month, Now.Day, 23, 59, 59) + New TimeSpan(3, 0, 0, 0)


Case DayOfWeek.Thursday


startDay = New DateTime(Now.Year, Now.Month, Now.Day) - New TimeSpan(4, 0, 0, 0)


endDay = New DateTime(Now.Year, Now.Month, Now.Day, 23, 59, 59) + New TimeSpan(2, 0, 0, 0)


Case DayOfWeek.Friday


startDay = New DateTime(Now.Year, Now.Month, Now.Day) - New TimeSpan(5, 0, 0, 0)


endDay = New DateTime(Now.Year, Now.Month, Now.Day, 23, 59, 59) + New TimeSpan(1, 0, 0, 0)


Case DayOfWeek.Saturday


startDay = New DateTime(Now.Year, Now.Month, Now.Day) - New TimeSpan(6, 0, 0, 0)


endDay = New DateTime(Now.Year, Now.Month, Now.Day, 23, 59, 59)


End Select


Dim cn As New SqlClient.SqlConnection(';'''INSERT YOUR CONNECTION DATA HERE'''';)


Dim sql As String = ';SELECT * FROM TABLE WHERE LOGDATE %26gt; @STARTDATE AND LOGDATE %26lt; @ENDDATE';


Dim cmd As New SqlClient.SqlCommand(sql, cn)


cmd.Parameters.AddWithValue(';@STARTDATE';鈥?startDay)


cmd.Parameters.AddWithValue(';@ENDDATE';, endDay)


End Sub





End Class





Alternatively, if you want to be able to pick the boundary days (like from a Tuesday to a Thursday, or from a Wednesday to a Wednesday) you could simply put 2 DateTimePicker controls on your form and pass their values to your query.





There are many other ways to do this, so if you want a different method, contact me and I've got more ideas.





I hope this helps.Date Ranges and vb.net?
The past week is today minus 6 days, the month is either today minus 29 days or 30 days or today but one month earlier (watch the rollover for January) and the year is either today minus 364 days (or 365 days for a leap year) or today but one year earlier. There's a date function that allows you to do arithmetic on dates (in VB) called DateDiff. Then you do a Select for the ';between'; dates you want.





(As an aside, the date is stored as in integer, regardless of how it's displayed.)