Getting data between dates

Question

So, I want to retrieve data between two dates in mssql. I have a table, name is FILES. And I have 2 column, names are NAME, RECORDDATE (nvarchar). When I save my data, I take from raddatetimepicker.text so my value is "22 December 2015 Tuesday".

My query is

select * from FILES where RECORDDATE>= '11 January 2015 Saturday' and RECORDDATE<= '24 October 2015 Monday'

This query is working but when I change '11 January 2015 Saturday' to '28 January 2015 Tuesday' it is not working. It is so interesting because my query with 21 January also working but when I write 28 January I got 0 rows.

I really stuck and no idea about problem.

Your answers are true but also others answers are true so I do not know how I can I mark all answers as an answer :) Thanks for that. That is my first project after 1 month self training so do not surprise so much :D

Answer 1

The following is true under the condition that your RECORDDATE is actually a DATETIME field in the database. You currently store what should be a DATETIME as an NVARCHAR. Stop doing this now and use proper types!

Any DateTimePicker should have a property that returns a DateTime. If it does not: dump it and use a proper one. That said, you can store as well as look-up your records properly using parameterized queries.

Inserting will look like this:

DateTime recordDate = dtpDate.Value;
using (SqlCommand cmd = new SqlCommand("insert into FILES (NAME, RECORDDATE) VALUES (@name, @recorddate)", conn))
{
    cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = someName;
    cmd.Parameters.ADd("@recorddate", SqlDbType.DateTime).Value = recordDate;
    cmd.ExecuteNonQuery();
}

Then the lookup query comes down to:

DateTime start = dtpStart.Value;
DateTime end = dtpEnd.Value;

using (SqlCommand cmd = new SqlCommand("select * from FILES where RECORDDATE BETWEEN @start and @end", conn))
{
    cmd.Parameters.Add("@start", SqlDbType.DateTime).Value = start;
    cmd.Parameters.Add("@end", SqlDbType.DateTime).Value = end;

    using (SqlDataReader reader = cmd.ExecuteReader())
        ...
}

Doing it this way solves the following problems:

1. SQL injection possible if you insert parameters using String.Format or something similar.
2. Different date formats don't matter, as the .NET will convert the date properly into something SQL server understands

Answer 2

Actually you get String comparison, not Date comparison, due to RECORDDATE is NVARCHAR (unicode String).

first string starts with '28..' so its bigger than string that starts with '24', so you got always false and therefore 0 rows.

Look at this answer: https://stackoverflow.com/a/1411081/2811743

Answer 3

Due to storing the dates as string you are performing string comparisons, not date comparisons. When you have updated your values your are checking for strings between 28 January 2015 Tuesday and 24 October 2015 Monday. Given that the lower value is greater you will never get any rows returned.

If you want to find values between two dates then preferably change your data type to DateTime.

If this is not possible then you could cast the column to a DateTime e.g.

SELECT * FROM FILES 
WHERE RECORDDATE >= CAST('11 January 2015 Saturday' AS DATETIME) 
    AND RECORDDATE <= CAST('24 October 2015 Monday' AS DATETIME)

As a sidenote 28 January 2015 is a Wednesday, not a Tuesday

A drawback to this approach is that you will not be able to use indexes on the RECORDDATE column as the query would be non-SARGable

Answer 4

In your comparison, you are comparing strings, not dates. In SQL string comparison is done based on alphabetical order. In your example, this means that '28...' > '24...'.

You should either

• change your database design to store your dates as DATETIME values, or
• CAST your nvarchar to DATETIME

As a side note, including the weekday is unnecessary to check for a date range so I'd recommend removing it to avoid mistakes. In fact, it appears that you have a mistake in your example: Tuesday should be Wednesday in your question.

Answer 5

Hopefully you can fix your data... but based on your example dates here is a CTE that will fix your dates and format your data so it can be filtered.

WITH [SubDates] AS (
    SELECT 
        [FILES].*
        ,SUBSTRING([FILES].[RECORDDATE], 0, PATINDEX('%20[0-9][0-9] %', [FILES].[RECORDDATE]) + 4) AS [RECORDDATE_Sub]
    FROM [FILES]
), [AsDates] AS (
    SELECT 
        [SubDates].*
        ,CASE ISDATE([SubDates].[RECORDDATE_Sub])
            WHEN 1 THEN CONVERT(DATE, [SubDates].[RECORDDATE_Sub])
            END AS [RECORDDATE_Fixed]
    FROM [SubDates]
)
SELECT * 
FROM [AsDates] 
WHERE 
    [AsDates].[RECORDDATE_Fixed] BETWEEN '11 January 2015' AND '24 October 2015'

Answer 6

Try this

select * from FILES where RECORDDATE between STR_TO_DATE('11 January 2015 Saturday',"%d %M %Y %W") and STR_TO_DATE('24 October 2015 Monday',"%d %M %Y %W")