SQL DateTime range search

Question

0.00/5 (No votes)

See more:

Getting error when running SQL to search for a range of records between month start and end.
SqlDataSource1 is defined for a GridView on web page.
---------------------------------------------------------------------------

protected void Page_Load(object sender, EventArgs e)
  {
      string sqltmp;
      if (!IsPostBack)
      {
          DateTime now = DateTime.Now;
          DateTime firstDate = new DateTime(now.Year, now.Month, 1);
          monthNumber = Now.Month;
          yearNumber = Now.Year;
          if (monthNumber == 12)
          {
                monthNumber = 1;
                yearNumber = yearNumber + 1;
          }
          else
          {
                monthNumber++;
          }
          DateTime lastDate = new DateTime(yearNumber, monthNumber, 1);

          sqltmp = "SELECT TOP 10 [Trans_Date], [CaseLast] ";
          sqltmp = sqltmp + "FROM Transactions ";
          sqltmp = sqltmp + "WHERE ";
          sqltmp = sqltmp + "( [Trans_Date] >= " + firstDate + " AND [Trans_Date] < " + lastDate + ")";

          SqlDataSource1.SelectCommand = sqltmp;
      }
  }

-------------------------------------------------------------------------------------
Built SQL looks like this:

"SELECT TOP 10 [Trans_Date], [CaseLast] FROM Transactions WHERE ( [Trans_Date] >= 5/1/2014 12:00:00 AM AND [Trans_Date] < 6/1/2014 12:00:00 AM)"

--------------------------------------------------------------------------------------
Error: Incorrect syntax near '12'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '12'.
---------------------------------------------------------------------------------------
What did I forget?

Posted 10-May-14 14:56pm

Knave46

Updated 10-May-14 17:43pm

sankarsan parida

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-05-11T10:09:00

Solution 1 by RyanDev is good, but it needs some extra information.

First of all: you shoudn't use query direct in code-behind because of SQL Injection[^]
How To: Protect From SQL Injection in ASP.NET[^]
Stop SQL Injection Attacks Before They Stop You[^]
SQL Injection and how to avoid it[^]

Secondly, use Stored Procedure (SP)[^] instead.
Walkthrough: Displaying Data Using a Stored Procedure in the GridView Web Server Control[^]
How to call SQL Server stored procedures in ASP.NET by using Visual Basic .NET[^]
How to: Execute a Stored Procedure that Returns Rows[^]

Finally, use BETWEEN[^] statement:

SQL

CREATE PROCEDURE GetSampleData
    @datefrom DATETIME,
    @dateto DATETIME
AS
BEGIN
    SELECT <FieldList>
    FROM <TableName>
    WHERE <DateField> BETWEEN @datefrom AND @dateto

ZurdoDev · Accepted Answer · 2014-05-10T15:19:00

Solution 1

It is because you do not have single quotes around your dates. However, this is a very dangerous way to write your code. You need to use parameters.

C#

String sql = "SELECT .. FROM ... WHERE [Trans_Date] BETWEEN @firstDate AND @lastDate
...
cmd.Parameters.AddWithValue(@firstDate, firstDate);
cmd.Parameters.AddWithValue(@lastDate, lastDate);
...

Posted 10-May-14 15:19pm

ZurdoDev

Comments

Maciej Los 11-May-14 16:10pm

Good, but not good enough ;) (voted 4!)
Please, see my answer.

Knave46 11-May-14 18:46pm

Question for RyanDev - under the condition that SqlDataSource1 is in the selection in the GridView setup of a web page what would be the syntax for:
cmd.Parameters.AddWithValue(@firstDate, firstDate);
cmd.Parameters.AddWithValue(@lastDate, lastDate);
It seems that these would only work if the code is doing the 'cmd' from inside the Page_Load function

ZurdoDev 11-May-14 21:02pm

I don't follow what you're saying but if you're asking how to add the parameters using a datasource defined in the aspx design instead of in the .cs code then you add SelectParameters to it.

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selectparameters(v=vs.110).aspx