The following is my SQL which is supposed to return records from the StartDate to the EndDate; in this case, the EndDate is 8/29/2009 11:59:59 PM. This is all part of a SSRS report that the user selects the dates from a calendar or runs with default dates. The report works fine except the EndDate is off 1. I boiled it down to the SQL. Here is an abbreviated version:DECLARE @Team Varchar(36)DECLARE @BeginDate DateTimeDECLARE @EndDate DateTimeSET @Team = 'West'SET @BeginDate = '8/23/2009'SET @EndDate = '8/29/2009 11:59:59 PM'SELECT DISTINCT pt_basic.patient_id, ptc_cert_period.certification_dateFROM pt_basic pt_basic INNER JOIN pt_admission pt_admission ON (pt_admission.patient_id = pt_basic.patient_id) INNER JOIN ptc_cert_period ptc_cert_period ON (ptc_cert_period.patient_id = pt_basic.patient_id) AND (ptc_cert_period.cert_end_date > pt_basic.last_certification_date) LEFT OUTER JOIN o_team o_team ON (o_team.team_id = pt_admission.team_id)WHERE (o_team.team_description = @Team) AND (ptc_cert_period.certification_date >= Convert(DATETIME,@BeginDate,120) AND ptc_cert_period.certification_date < Convert(DATETIME,DateAdd("D",1,@EndDate),120))This code runs as listed here, except getting records one day later than it shoud. I do want to get everything up to 11:59 PM that day, though. Thanks for any help. I found out it only happens if the time entered is exactly midnight. If a time even one minute after midnight is entered for the EndDate, it works fine. Now, as I said, these dates are coming from Reporting Services default dates and somehow, the default date adds the 11:59:59 to the date selected. It must get that directly from THIS sql, because it is not coded in the report. Please help me here. Thank you.Duane
EndDate returns records one day late
Posted - 2009-08-21 : 10:57:41
