Comparing dates in SQL

The SQL datetime type stores a value like

2013-06-25 00:00:00.000

Let’s say you have a table called ‘Log’ and you log actions to it and pass in the date time which is stored in the ‘LogDate’ column (type = datetime).

It will have rows with values like

2013-06-25 01:12:46.123

2013-06-25 02:13:09.111

2013-06-25 02:14:44.676

Now you want to get the rows from the table from ’06/25/2013′ so you preform a query like

Select * from Log where LogDate = ’06/25/2013′

You expect 3 rows of data, but you get none because the above query would only return rows where the Date field was ‘ 2013-06-25 00:00:00.000 ‘.

2 methods I like to solve this are either change the query to

Select * from Log where LogDate >= ’06/25/2013’

AND LogDate < ’06/26/2013

 

OR, I would create a Stored Proceduret that has in input parameter of type ‘date’ (not datetime).

Create PROCEDURE [dbo].[usp_SelectAllLogsByDate]
@myDate date  /*Don’t use datetime here or your are back to the original problem*/

As

Select * FROM [Log]
where (Date = @myDate)

 

There are other solutions using the date functions in SQL however, but these are 2 quick and simple solutions.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s