SQL/ASP.NET - Select Month/Year Date Range

Sorry, I should have specified! :)  We are constructing the queries directly in the VBScript by creating strings.  Example....  Dim strSQLQuery As String = "SELECT * FROM TABLE WHERE COLUMN = " & Me.ddlDropDown.SelectedValue.ToString & ";"

You can convert your selected month/year into a normal date and then use that to do the query.
For example:
month=1
Year=2005
month2=2
Year2=2006
startDate= cdate(month & "/1/" & year)
endDate= cdate(month2 & "/1/" & year2)
Now since SQL will assume midnight on a date with no time, you can then query by looking for anything that is greater than or equal to the startDate and less than the endDate as that will yield everything between startDate at midnight to endDate at midnight.
dim sql="Select * from table where myDate >= '" & startDate & "' and myDate < "'" & endDate & "'"
And actually the right way would be to call a stored procedure instead such as.
Create Procedure TicketsByDate
    @startDate as smallDateTime,
    @endDate as smallDateTime
as
    Select
          *
     From
         TICKETS_VIEW
     Where
         DT_CREATED >= @startDate AND
         DT_CREATED  < @endDate
     Order By
         DT_CREATED Desc
GO
Then you could use the sqlParameter object to pass in the parameters or simply pass it something like this:
TicketsByDate "'" & startDate & "','" & endDate & "'"
which should look like this to the sql server.
TicketsByDate '1/1/2005','2/1/2006'
If you need more information on using sqlParameters see the following articles:
http://www.codeproject.com/KB/database/NET_Data_Access.aspx
http://www.codeproject.com/KB/database/data_accessor_class.aspx
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx
Hope that helps.
 

set up the sql that you generate to use the between

so you generate the sql

String = "SELECT * FROM TABLE WHERE COLUMN  between  " & 

Me.ddlDropDown.SelectedValue.substring(1,2) + "/1/" + Me.ddlDropDown.SelectedValue.substring(2,2)

& " and "

Me.ddlDropDown1.SelectedValue.substring(1,2) + "/1/" + Me.ddlDropDown1.SelectedValue.substring(2,2)

"We are constructing the queries directly in the VBScript by creating strings"

Why would you want to do that?


Regardless, when it comes to SQL if you don't explicity convert the date string to a specific format, you will be leaving it up to SQL Server to determine how it should be parsed.  I don't know what drop down box goes into each value, but if you replace BeginMonth, BeginYear, EndMonth and EndYear with their respective form values, this will generate an inline SQL statement that does exactly what my procedure was doing above.

SQL = "select * from tickets_view" & _
" where dt_created >= convert(datetime, '1/' + cast(" & BeginMonth & " as varchar(2) + '/' + cast(" & BeginYear & " as char(4), 101) " & _
"  and dt_created < dateadd(m, 1,convert(datetime, '1/' + cast(" & EndMonth & " as varchar(2) + '/' + cast(" & EndYear & " as char(4), 101))"

SELECT * FROM [TICKET_VIEW] WHERE ([CLIENT_ID] = 29) AND dt_created >= convert(datetime, '1/' + cast(2 as varchar(2) + '/' + cast(2008 as char(4), 101) and dt_created < dateadd(m, 1,convert(datetime, '1/' + cast(12 as varchar(2) + '/' + cast(2008 as char(4), 101)) ORDER BY DT_CREATED DESC


BrandonGalderisi...I don't know why we aren't using stored procedures!  That's just what we are doing! :)  I tried your suggestion and used Feb/2008 and Dec/2008 as you'll see in the query above.  This was the actual query that was run.  It says "System.Data.SqlClient.SqlException: Incorrect syntax near '+'."  when I run it.

Emes and Arlowin, I don't think your queries will work because you are using "1" in the date.  For example, if I give it Feb/2008 as the end date, I want it to select ALL the days within that month.  So selecting Feb/2008 to Dec/2008 will give everything with a date of 2/1/08 - 12/31/08.  Of course the problems is that you don't know how many days are in the particular month.

Here's the query being built...

strTicketSQL = "SELECT * FROM [TICKET_VIEW] WHERE ([CLIENT_ID] = " & Request.QueryString("ID") & ") AND " & _
                    "dt_created >= convert(datetime, '1/' + cast(" & Me.ddlMonth.SelectedValue.ToString & " as varchar(2) + '/' + cast(" & Me.ddlYear.SelectedValue.ToString & " as char(4), 101) " & _
                    "  and dt_created < dateadd(m, 1,convert(datetime, '1/' + cast(" & Me.ddlMonth2.SelectedValue.ToString & " as varchar(2) + '/' + cast(" & Me.ddlYear2.SelectedValue.ToString & " as char(4), 101)) " & _
                    "ORDER BY DT_CREATED DESC"

Actually I did test the query before I posted it, and it will work.

The nice thing about querying that way is you don't need to know the number of days in the month.

If I:
Select * from table where myDate >= StartDate and myDate < endDate

And Start Date = 2/1/2008 and endDate = 1/1/2009 it will return everything from 2/1/2008 to 12/31/2008.  Its actually looking at the time too, so SQL is seeing this as 1/1/2009 00:00:00 and 1/1/2009 00:00:00, and guess what..one second before 1/1/2009 at 12am its 12/31/2008 11:59:59 which is less than endDate so it return not only every day in between but all times in between too.

At any rate, its sometimes easier to build the sql statement in parts than to try and slam it all together in one big string.

So I suggest building each date first, and then build the rest of the sql statement around that, it will make it simpler to put together.  Then if you want to optimize it, after its working, sure put it all back into one statement.

And yes SQL can handle the date conversion for you, but its a heck of a lot easier to do it in .NET and then just pass it the dates.

Ok since I'm being long winded, I took your sql statment and made a web page with the controls mentioned in it and then made it create a sql statment for me.  I took what it made and pasted that into query analyzer and had it check the syntax, and sure enough it had some syntax errors.

See attached code:

Ok, now lets look at a simpler solution.

        Dim strTicketSQL As String
        Dim startDate As Date = CDate(ddlMonth.SelectedValue & "/1/" & ddlYear.SelectedValue)
        Dim endDate As Date=CDate(ddlMonth2.SelectedValue & "/1/" & ddlYear2.SelectedValue)

        strTicketSQL = "SELECT * FROM [TICKET_VIEW] WHERE "
        strTicketSQL += "([CLIENT_ID] = " & Request.QueryString("ID") & ") AND "
        strTicketSQL += "dt_created >=  '" & startDate & "'"
        strTicketSQL += " and dt_created < '" & endDate & "'"
        strTicketSQL += " ORDER BY DT_CREATED DESC"

Which generates:

        SELECT * FROM [TICKET_VIEW]
        WHERE ([CLIENT_ID] = 1) AND
        dt_created >= '1/1/2005' and
        dt_created < '1/1/2006'
        ORDER BY DT_CREATED DESC

This does pretty much the same thing, but its a whole lot easier to code and debug.

Now the only thing beyond this I feel compelled to mention here is this.  Your getting your ID from a query string paramenter "ID" which is very easy to take advantage of in a SQL injection attack.

Hope that helps,

Arlowin

Your statement builds this:
SELECT * FROM [TICKET_VIEW] WHERE ([CLIENT_ID] = 1) AND dt_created >= convert(datetime, '1/' + cast(1 as varchar(2) + '/' + cast(2005 as char(4), 101) and dt_created < dateadd(m, 1,convert(datetime, '1/' + cast(1 as varchar(2) + '/' + cast(2007 as char(4), 101)) ORDER BY DT_CREATED DESC
 
You're missing single quotes around the numbers and closing parentheses around the cast statements.  Here it is fixed:
SELECT * FROM [TICKET_VIEW] 
WHERE ([CLIENT_ID] = 1) AND 
dt_created >= convert(datetime, '1/' + cast('1' as varchar(2)) + '/' + cast('2005' as char(4)), 101) and 
dt_created < dateadd(m, 1,convert(datetime, '1/' + cast('1' as varchar(2)) + '/' + cast('2007' as char(4)), 101)) 
ORDER BY DT_CREATED DESC
 
Also from your statements above it sounds like the values of your drop down lists are actually Feb,Mar, Apr etc..those should be set to numbers if you don't have it that way, here is my drop down list(s).
<asp:DropDownList ID="ddlMonth" runat="server">
            <asp:ListItem Value="1">Jan</asp:ListItem>
            <asp:ListItem Value="2">Feb</asp:ListItem>
            <asp:ListItem Value="3">Mar</asp:ListItem>
            <asp:ListItem Value="4">Apr</asp:ListItem>
            <asp:ListItem Value="5">May</asp:ListItem>
            <asp:ListItem Value="6">Jun</asp:ListItem>
            <asp:ListItem Value="7">Jul</asp:ListItem>
            <asp:ListItem Value="8">Aug</asp:ListItem>
            <asp:ListItem Value="9">Sep</asp:ListItem>
            <asp:ListItem Value="10">Oct</asp:ListItem>
            <asp:ListItem Value="11">Nov</asp:ListItem>
            <asp:ListItem Value="12">Dec</asp:ListItem>
        </asp:DropDownList>
        <asp:DropDownList ID="ddlYear" runat="server">
            <asp:ListItem>2005</asp:ListItem>
            <asp:ListItem>2006</asp:ListItem>
            <asp:ListItem>2007</asp:ListItem>
            <asp:ListItem>2008</asp:ListItem>
            <asp:ListItem>2009</asp:ListItem>
        </asp:DropDownList>

Select allOpen in new window

Thanks, that gave me something, but it wasn't returning the right data...or sometimes nothing at all.  I did some tweaking, made it a bit more simple, and came up with the solution! :)  The concept of adding a month to the end month selected was a great idea!  And yes, I did use numbers for the values rather than month names in the drop-downs.  Since none of the help given solved the problem directly, I won't award points.  Hope that's ok! :)


strTicketSQL = "SELECT * FROM [TICKET_VIEW] WHERE ([CLIENT_ID] = " & Request.QueryString("ID") & ") AND " & _
                    "dt_created >= '" & Me.ddlMonth.SelectedValue.ToString & "/1/" & Me.ddlYear.SelectedValue.ToString & "' " & _
                    "  and dt_created < dateadd(m, 1, '" & Me.ddlMonth2.SelectedValue.ToString & "/1/" & Me.ddlYear2.SelectedValue.ToString & "') " & _
                    "ORDER BY DT_CREATED DESC"

Since none of the help given solved the problem directly, I won't award points.  Hope that's ok! :)

No that's not really OK.  

The Query you are using did help solve the problem directly.  You are doing the next month as I showed in http:#23299394 which also tells what can happen if you don't explicitly provide a date format.  The only thing missing is your values in http:#23299394 which I described is because I wasn't sure which dropdown list was for each value.

Write your SQL out to screen from http:#23301247 and mine from http:#23299394 and you will see that they are VERY similar.  Mine doesn't have clientID but your original query didn't mention it.

I don't really find it to be "OK" either.  Somone should get the points.