Last post Feb 24, 2016 08:43 AM by PatriceSc
Feb 23, 2016 11:50 AM|pn_nq|LINK
Feb 23, 2016 12:00 PM|Lokesh B R|LINK
Select * from tableA where startdate <= convert(date, @today, 101) and enddate >= convert(date, @today, 101)
Feb 23, 2016 12:10 PM|PatriceSc|LINK
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.
Feb 23, 2016 03:04 PM|pn_nq|LINK
Feb 23, 2016 03:34 PM|PatriceSc|LINK
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?
Feb 24, 2016 12:11 AM|oned_gk|LINK
Select * from tableA where CAST(GetDate() as Date) BETWEEN startdate and enddate
or pass @today as date
DateTime today = DateTime.Now.Date();
Feb 24, 2016 02:15 AM|pn_nq|LINK
Feb 24, 2016 08:43 AM|PatriceSc|LINK
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?