Today one of my user complained that a report was missing one particular ticket from the output. The report in question is very simple, it accepts start date & end date and filters on the ticket creation date time column to get the list of tickets.
The report query was something like
Select * from table1 where convert(datetime,CONVERT (int, CreatedDate)) between ‘2012-01-01’ AND ‘2012-01-31’
Cursory look at the query looked fine. However I was proved wrong when I ran the query in SSMS. It was missing a ticket whose CreatedDate was ‘2012-01-31 18:57’
After some troubleshooting , I figured out that the issue was due to the conversion of CreatedDate to INT. In the query, the date was first converted to INT to remove the time part and then again converted to date time for comparison.
When the time part is in PM, the conversion to INT, converts it to next day. The below screenshot will explain it more precisely
Handling DateTime in SQL SERVER is very tricky especially when you are some processing like conversion, finding datediff etc. The only option we have is to test it out thoroughly.
