Select by date
-
Hi, I am trying to get results where the parameter that is passed is a date. I am only passing the date itself and not the time although the field in the datebase holds both. How can I retreve date specific results and ignore the time. I currently have
WHERE E.Start = @DateBut it seems to be passing @Date with the time 00:00:00 at the end which obviously makes the wrong, if any, results comeback. Any ideas appreciated. Thanks -
Hi, I am trying to get results where the parameter that is passed is a date. I am only passing the date itself and not the time although the field in the datebase holds both. How can I retreve date specific results and ignore the time. I currently have
WHERE E.Start = @DateBut it seems to be passing @Date with the time 00:00:00 at the end which obviously makes the wrong, if any, results comeback. Any ideas appreciated. ThanksSQL Server does not have a data type which only holds date information, only ones which hold date and time information. In practice if you want any time on a specific day you have to say:
WHERE E.Start >= @Date AND E.Start < DATEADD( d, 1, @Date )
Stability. What an interesting concept. -- Chris Maunder
-
SQL Server does not have a data type which only holds date information, only ones which hold date and time information. In practice if you want any time on a specific day you have to say:
WHERE E.Start >= @Date AND E.Start < DATEADD( d, 1, @Date )
Stability. What an interesting concept. -- Chris Maunder
Yeah I was stupid enough to post without looking three post down where the answer was already written. I came up with something pretty much like you suggested
DECLARE @FromDate datetime DECLARE @ToDate datetime SET @FromDate = (CAST(@Date AS datetime )) SET @ToDate = DATEADD( D, 1, (DATEADD(mi, -1, @FromDate )) ) SELECT E.ID As EventID, ET.ID As TypeID, E.Title As EventTitle, V.ID As VenueID, V.[Name] As VenueName, E.Description As EventDescription, E.Start As EventStart, E.Finish As EventFinish FROM Events E INNER JOIN Venues V ON E.VenueID = V.ID INNER JOIN EventTypes ET ON E.TypeID = ET.ID WHERE E.Start >= @FromDate AND E.Start <= @ToDate AND V.CityID = @CityIDThanks anyways -
Hi, I am trying to get results where the parameter that is passed is a date. I am only passing the date itself and not the time although the field in the datebase holds both. How can I retreve date specific results and ignore the time. I currently have
WHERE E.Start = @DateBut it seems to be passing @Date with the time 00:00:00 at the end which obviously makes the wrong, if any, results comeback. Any ideas appreciated. ThanksYou have two options of rephrasing your WHERE clause. Each solution assumes that both @Date and E.Start are declard as DATETIME: (1)
WHERE E.Start BETWEEN @Date AND DATEADD(d, 1, @Date)This will look for E.Start values between midnight on @Date and midnight on @Date + 1 day (2)WHERE DAY(E.Start) = DAY(@Date) AND MONTH(E.Start) = MONTH(@Date) AND YEAR(E.Start) = YEAR(@Date)This will explicitly compare the month, day, and year components of each field and only flag those where all three are the same.