DateTime - Month to date

[jonathen]

I wonder if someone can help me get my head around a problem I have which should be simple to work out, but it's making my head spin!

I have an ASP.NET form which gets a variable from the querystring for the period - it'll either be: M, LM or 2M (month to date, last month, 2 months ago).

What I am trying to do is get a variable which I can pass to a SQL statement that will get all records from a database where the dates come under one of the above 3 dates.

So for example, if the period is M (month to date) then I can select all records from the table where the dates are within the current month.

I can figure out the LM and 2M by using DateTime.Now.Month - 1 or DateTime.Now.Month - 2, but where do I go from there?

I hope this makes sense...

Thanks in advance for any advice

 

[kulrom]

There are many ways to accomplish the same thing but say you want to go for the example above (Now.Month-2).
Means as you said:

Dim myDate as date = DateTime.Now.Month - 2

SQL statement should be

strSQL = String.Format("SELECT field1, field2 FROM myTable WHERE FieldDate BETWEEN DateValue('{0}') AND DateValue('{1}')", Now, mydate)

'or you can also use statement where fieldDate < myDate

also for the other conditions

HTH
Regards

 

[jonathen]

Thanks for your help. Not sure if I understand properly, as I am unable to get this to work. I've posted my code below - I'm not sure what do put for Case "M" - how do I get it to figure out month to date?

    Dim SqlConnection As String
        Dim SqlCommand As SqlDataAdapter
        Dim urlPeriod As String
        Dim urlMember As String
        Dim myDate As Date

    urlPeriod = Request.QueryString("period")
        urlMember = Request.QueryString("member")
        SqlConnection = ConfigurationSettings.AppSettings("DBConnect")

        Select Case urlPeriod
            Case "M"
                myDate = DateTime.Now
            Case "LM"
                myDate = DateTime.Now.Month -1
            Case "2M"
                myDate = DateTime.Now.Month -2
        End Select

        SqlCommand = New SqlDataAdapter("SELECT ref AS 'Ref'," _
& "house+' '+street+' '+district AS 'Address' FROM mytable WHERE "_
& "member = '" & urlMember & "' AND " _
& "recdate BETWEEN DateValue('{0}') AND DateValue('{1}'), Now, myDate " _
& "ORDER BY recdate ASC", SqlConnection)

        Dim dsStats As DataSet = New DataSet
        SqlCommand.Fill(dsStats)

        dgStats.DataSource = dsStats
        dgStats.DataBind()

 

[kulrom]

you will either use a string.Format or just a string ... you can't mix them up
Means either add string.Format ahead of select statement or remove dataValues and parameters replacing with simple WHERE condition

Regards