Sql Date and Time Comparison

by Mandeep on Feb 14, 2019 in Sql

Sql date and time can get tricky. Partially, due to our state of mind pre-occupied with the semantics of string and number comparison.

Please bookmark this page for date and time comparison formats. It will come handy.

Things to remember -

  1. Do not convert your date column to string. This is a pretty common mistake
  2. Avoid using 'between' for comparison

If you are doing only 'date' comparison then you have a lesser change of a screw-up.

Taking an example for date and time comparison. Notice - we did not convert our column data. Instead, we converted the input string.

select top 100 * from dbo.Log (nolock) 
where url like '%SOME_URL%' and LoggedDateTime >
CONVERT(DateTime, '2019-02-14 06:15:00') and LoggedDateTime < 
CONVERT(DateTime, '2019-02-14 08:15:00')

Hopefully, it'll help you in coming out of trenches.

 

 

Add comment