locked
sql date comparision problem RRS feed

  • Question

  • User-804681621 posted
    Select * from tableA where startdate <= @today and enddate >=@today


    I dont know why I can't do the comparision , maybe the date format is different or data type is different. How to fix the problem so that I can't select the record with @today is between start date and end date ?
    Startdate and enddate days type is date with format yyyy-mm-dd
    And @today days type is varchar with format yyyymmdd
    Tuesday, February 23, 2016 11:49 AM

Answers

  • User753101303 posted

    pn_nq

    specific format

    This is why I ask about the type you are using. If done properly (e using dates rather than strings) you don't have to care about the format. If using a string it could be an issue but for now it is still unclear if this value is passed as a string or as a date to the SQL side.

    You don't have told also if you are using just a date or if you have a time as well.. you could try maybe something such as :

    DECLARE @Today SMALLDATETIME
    SET @Today='20160115' -- Or passed as a date from C#
    ;WITH SampleData AS (
    	SELECT CAST('20160114 8:00' AS SMALLDATETIME) AS StartDate,CAST('20160114 10:00PM' AS SMALLDATETIME) AS EndDate
    	UNION ALL SELECT '20160115 8:00','20160115 10:00PM'
    	UNION ALL SELECT '20160116','20160117'
    )
    SELECT * FROM SampleData
    WHERE StartDate>=@Today AND EndDate<DATEADD(day,1,@Today)

    to understand what is wrong or to show a sample of your problem.

    Edit: or another option would be that this criteria is not what you really want? Let's start from your exact need. So do you have events with a start and end date & time and you want to select all events who are happening (even partially) on a given day?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 24, 2016 8:43 AM

All replies

  • User1577371250 posted

    Hi,

    try this

    Select * from tableA where startdate <= convert(date, @today, 101) and enddate >= convert(date, @today, 101)

    Tuesday, February 23, 2016 12:00 PM
  • User753101303 posted

    Hi,

    I can't do the comparision

    That is? What happens? Do you have an error message? Or your query doesn't return what you expect ? A date is a date and doesn't have a format. A format is just used when showing the value. It could be that you are uisng a wrong type or it could be also that you have a time portion inside your date causing the criteria to be false. Please always be explicit about what happens.

    Tuesday, February 23, 2016 12:10 PM
  • User-804681621 posted

    No error, but it seems retrieve the wrong data. So I guess it cant compare the date is equal to / after or before @today
    Tuesday, February 23, 2016 3:04 PM
  • User753101303 posted

    First @today is a date or a string ?  Make sure it is a date already. Also do you have a time portion in your data? For example to select a date with a time portion my approach is to do something such as myDate>=@Today AND @myDate<DATEADD(day,1,@Today) that is between the beginning of the day and before the very beginning of the next day.

    For now you select rows if a particular date/time maybe falls inside an event and my guess is that maybe you are testing something that goes from 8:00 AM to 17:00 PM against today at 00:00 which doesn't fall in this interval.

    It is important to understand first what is the exact problem. First are you using only date types and not strings?

    Tuesday, February 23, 2016 3:34 PM
  • User-1716253493 posted
    Select * from tableA where CAST(GetDate() as Date) BETWEEN startdate and enddate

    or pass @today as date

    DateTime today = DateTime.Now.Date();
    cmd.Parameters.AddWithValue("@today", today);

    Wednesday, February 24, 2016 12:11 AM
  • User-804681621 posted
    I still can't select the correct record as I can see some end date larger than @today, but can't be selected.

    It only select the end date smaller or equal to end date.

    Is it needed to convert to specific format when comparing date?
    Wednesday, February 24, 2016 2:15 AM
  • User753101303 posted

    pn_nq

    specific format

    This is why I ask about the type you are using. If done properly (e using dates rather than strings) you don't have to care about the format. If using a string it could be an issue but for now it is still unclear if this value is passed as a string or as a date to the SQL side.

    You don't have told also if you are using just a date or if you have a time as well.. you could try maybe something such as :

    DECLARE @Today SMALLDATETIME
    SET @Today='20160115' -- Or passed as a date from C#
    ;WITH SampleData AS (
    	SELECT CAST('20160114 8:00' AS SMALLDATETIME) AS StartDate,CAST('20160114 10:00PM' AS SMALLDATETIME) AS EndDate
    	UNION ALL SELECT '20160115 8:00','20160115 10:00PM'
    	UNION ALL SELECT '20160116','20160117'
    )
    SELECT * FROM SampleData
    WHERE StartDate>=@Today AND EndDate<DATEADD(day,1,@Today)

    to understand what is wrong or to show a sample of your problem.

    Edit: or another option would be that this criteria is not what you really want? Let's start from your exact need. So do you have events with a start and end date & time and you want to select all events who are happening (even partially) on a given day?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 24, 2016 8:43 AM