locked
Querying between Date ranges RRS feed

  • Question

  • User-297332451 posted

    I need to write a query like below in C#

    Select  * from Tablename where [carpark].[dbo].[PERDATA].DATE_ISSUED Between 11/04/1997 and 26/06/1998

    so date will be  in dd/mm/yyyy format in the sql table date field is of type datetime

    Wednesday, January 20, 2016 1:53 PM

Answers

  • User281315223 posted

    You mention that the date would be entered in using the 'dd/MM/yyyy' format. If that is the case, then what you would want to do is actually pass DateTime objects as parameters into your query and executing it that way (using parameterization).

    You can see an example of this below :

    using(var connection = new SqlConnection("Your Connection String"))
    {
         // Build your query (notice the parameters)
         var query = "SELECT * FROM TableName WHERE [carpark].[dbo].[PERDATA].DATE_ISSUED BETWEEN @Start AND @End";
         using(var command = new SqlCommand(query,connection))
         {
                // Parse your DateTime values using the proper format as parameters
                var start = DateTime.ParseExact(StartTextBox.Text,"dd/MM/yyyy", null);
                var end = DateTime.ParseExact(EndTextBox.Text,"dd/MM/yyyy", null);  
    
                // Add your parameters to the query
                command.Parameters.AddWithValue("@Start",start);
                command.Parameters.AddWithValue("@End",end);
    
                // Get your results within a reader (or through some other approach)
                using(var reader = command.ExecuteReader())
                {
                      // Read here
                }          
         }
    }

    Using parameters will not only protect you from nasty things like SQL Injection attacks, but it will also allow .NET to properly format your date time objects into the appropriate SQL for the queries being executed.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 20, 2016 2:13 PM

All replies

  • User753101303 posted

    Hi,

    Use rather SQL parameters: http://www.csharp-station.com/Tutorial/AdoDotNet/Lesson06

    So you'll have something such as:

    Select  * from Tablename where [carpark].[dbo].[PERDATA].DATE_ISSUED Between @Start and @End. And then you'll provide the date values for those parameters. This way you never have to care about writing down those date using a format suitable for SQL Server.

    To be on the safe side SQL Server side doesn't care about the format but about the value. I meant that a single date stored in SQL Server could be shown using multiple formats because each country have its own. Still inside the db this is the same value.

    Tuesday, January 19, 2016 5:12 PM
  • User2103319870 posted

    If you have values in different format then you can convert it using DateTime.ParseExact or Convert Method

    //If you have values in different dateformat then you can convert it 
    string date1 = "11/04/1997";
    DateTime formattedtime1 = DateTime.ParseExact(date1, "dd/MM/yyyy", CultureInfo.InvariantCulture);
    //Get the value here
    string date2 = "26/06/1998";
    DateTime formattedtime2 = DateTime.ParseExact(date2, "dd/MM/yyyy", CultureInfo.InvariantCulture);   

    Tuesday, January 19, 2016 5:17 PM
  • User-1716253493 posted

    Maybe your asp.net and sql has deferent datetime format.

    Use parameterize query like all above suggestions,

    So, you only need to declare datetime varibale in asp.net then pass it.

    If there is a time value in date_issued , you can cast it as a date

    Select  * from Tablename where CAST([carpark].[dbo].[PERDATA].DATE_ISSUED as Date) Between @datestart and @dateend

    Wednesday, January 20, 2016 12:38 AM
  • User-297332451 posted

    I am trying to do a query to get records between two dates like

    Select * from TableName where [carpark].[dbo].[PERDATA].DATE_ISSUED  Between '11/11/1997 00:00:00' and '11/01/1998 00:00:00'

    DATE_ISSUED is of Datetime datatype , date will be supplied from textbox in dd/mm/yyyy format what is the best way to do this ,using an inline query.

    if I enter it as dd/mm/yyyy sql query reads it as mm/dd/yyyy

    Wednesday, January 20, 2016 1:53 PM
  • User281315223 posted

    You mention that the date would be entered in using the 'dd/MM/yyyy' format. If that is the case, then what you would want to do is actually pass DateTime objects as parameters into your query and executing it that way (using parameterization).

    You can see an example of this below :

    using(var connection = new SqlConnection("Your Connection String"))
    {
         // Build your query (notice the parameters)
         var query = "SELECT * FROM TableName WHERE [carpark].[dbo].[PERDATA].DATE_ISSUED BETWEEN @Start AND @End";
         using(var command = new SqlCommand(query,connection))
         {
                // Parse your DateTime values using the proper format as parameters
                var start = DateTime.ParseExact(StartTextBox.Text,"dd/MM/yyyy", null);
                var end = DateTime.ParseExact(EndTextBox.Text,"dd/MM/yyyy", null);  
    
                // Add your parameters to the query
                command.Parameters.AddWithValue("@Start",start);
                command.Parameters.AddWithValue("@End",end);
    
                // Get your results within a reader (or through some other approach)
                using(var reader = command.ExecuteReader())
                {
                      // Read here
                }          
         }
    }

    Using parameters will not only protect you from nasty things like SQL Injection attacks, but it will also allow .NET to properly format your date time objects into the appropriate SQL for the queries being executed.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 20, 2016 2:13 PM
  • User753101303 posted

    Hi,

    Use Between '1997-11-11' and '1998-01-11'

    The YYYY-MM-DD format doesn't depend on SQL Server settings :

    set dateformat dmy
    SELECT MONTH('11/01/1998'),MONTH('1998-11-01')
    set dateformat mdy
    SELECT MONTH('11/01/1998'),MONTH('1998-11-01')

    shows 1 or 11 for the first column but always 11 for the second column.

    I believe you asked the same question previously and I suggested to use parameters and values using their native value (ie here as DateTime values). This way the right thing is done for you. It helps also to avoid SQL injection attacks.

    The problem is that programming languages are allowing to give values in an unambigious way. But keep in mind that this is very special to developers and if I'm showing a string such as 01/02/2016 or even 1,234 you have basically no way to know the correct value if I'm not telling you which country convention should be applied (it could be January 2 or February 1st and 1234 or 1.234 depending on the country convention).

    Edit: so it was http://forums.asp.net/p/2082996/6011947.aspx?Querying+between+Date+ranges where you already had answers. Please avoid to post the same question multiple times.

    Wednesday, January 20, 2016 2:20 PM