I have requirement - to retrieve order from database that were created before 12:00 AM GMT on June 3, 2013.
I'm using datediff but it is giving me couple records that were created on June 3rd 2.59 AM also,
below is my code of line-
datediff(second,[WorkForce_JobPosting].[Job Posting Create Date_JP] ,'2013-06-03 12:00:00.000')> 0 )
note the date format for thhis create date in DB is 'date time'
'2013-06-03 12:00:00.000' is noon on 6/3 so any 6/3 date prior to NOON on that date will be in the result set.
It should be
--midnight the next day
SELECT datediff(second,'2013-06-03 02:59:00.000' ,'2013-06-03 00:00:00.000')
--the last second of the current day
SELECT datediff(second,'2013-06-03 02:59:00.000' ,'2013-06-02 23:59:59.999')
These both return negative values as you are expecting for that particular date input.