CONVERT(DATETIME,
	    LTRIM(
	        CONVERT(CHAR(20), DATEADD(d, -1, dATEADD(dd,DATEDIFF(dd,0,dbo.BVActions.[OrderedTime]),0)), 112)
	    ) + ' 12:30:01 PM'
	)

quote:

---

Originally posted by swenri


Hi All,

Hope you are all doing well in the heat and summer

I’m facing issue with regards to date, like I need to select orders which are placed between yesterday 12:31:00 PM to 06:30:00 AM , this report runs @ 07:00:00 AM today morning.

I had written a query as mentioned below.

SELECT
DISTINCT
dbo
.MO_Demographics.MRN AS MRN_#,
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName) AS Patients_Name,
dbo
.BVActions.IPR_Display,
dbo
.BVActions.scheduleTime,
dbo
.BVActions.scheduleString,
dbo
.BVActions.orderedTime,
dbo
.BVActions.ForEveryText,
dbo
.bllocation.[location_name] AS Location_Name
--dbo.BLSession_pat.[PatientClass] AS In_Out_Patient
FROM
dbo.bllocation LEFT OUTER JOIN dbo.BLPatient_Location
ON
dbo.bllocation.location_num= dbo.BLPatient_Location.location_num
LEFT
OUTER JOIN dbo.MO_Demographics ON
dbo.BLPatient_Location.sessionID=dbo.MO_Demographics.SessionID
LEFT
OUTER JOIN dbo.BVActions
ON
dbo.MO_Demographics.SessionID = dbo.BVActions.SessionID
--LEFT OUTER JOIN dbo.BLSession
--INNER JOIN dbo.BLSession
--ON dbo.BVActions.SessionID = dbo.BLSession.SessionID
--LEFT OUTER JOIN dbo.BLSession_pat
--ON dbo.BLSession_pat.SessionID = dbo.BLSession.SessionID
WHERE why is the next line commented out?
--dbo.BVActions.[orderedTime]
--BETWEEN ('2012-07-04 00:00:00') AND ('2012-07-06 00:00:01')
BETWEEN CONVERT(datetime,ltrim(CONVERT(char(20),DATEADD(d,-1,dbo.BVActions.[OrderedTime] why isn't this getdate()?),100))+ '12:30:01 PM')
AND CONVERT(DATETIME,ltrim(CONVERT(char(20),GETDATE(),100))+'06:30:00 AM')
--AND dbo.BVActions.[ForEveryText] IN (' (Collect: Lab)',' (Lab 7:00)')
--AND dbo.BVActions.scheduleString IS NOT NULL
--AND dbo.BLSession_pat.[PatientClass] IN ('I')
dbo
.bllocation.[location_name] IS NOT NULL
AND
dbo.MO_Demographics.MRN IS NOT NULL


Even though I use DATEADD function in T-SQL 2005 it is not giving me the correct result. It is giving me all the orders instead of orders only the orders for 1 day. Can anybody help me with this please it’s urgent.

---

WHERE dbo.BVActions.[orderedTime] BETWEEN DATEADD(dd,datediff(dd,0,getdate()),-0.5 + 31.0/(24*60))
AND DATEADD(dd,datediff(dd,0,getdate()),0.25)