Working With Dates and Times in Sql Server
The DateTime field in Sql Server stores both a date and a time.
Let's say you have DateTime field with these values
2010-01-01 00:00:00.000
2010-01-01 05:00:00.000
2010-01-01 20:00:00.000
2010-01-01 23:59:59.000
2010-01-02 00:00:00.000
2010-01-02 00:00:01.000
Note that all the dates are on January 1st except the last 2 which are on January 2nd.
Now let's say I want to write a query to retreive all the rows with dates on January 1st.
I could do this in my Where Clause like this:
WHERE
DateField between
Convert(DateTime, '1/1/2010')
and
Convert(DateTime, '1/2/2010')
Since between is inclusive (meaning equivalent to greater than or equal to and less than or equal to),
the set that is returned is:
2010-01-01 00:00:00.000
2010-01-01 05:00:00.000
2010-01-01 20:00:00.000
2010-01-01 23:59:59.000
2010-01-02 00:00:00.000
Which is basically correct except it includes the date from midnight on the 2nd which is not what I wanted.
Now depending on your application, you may be able to live with this inaccuracy.
However, if your app needs to be completely accurate, you can change your query to:
WHERE
DateField >= Convert(DateTime, '1/1/2010')
and
DateField < Convert(DateTime, '1/2/2010')
In our example >= is used to capture the data at midnight on the 1st and < is used to not capture the data from midnight on the 2nd.
Note that if your application is giving a user the ability to select a date range, meaning a start date and an end date, you will need to make adjustments to their entry.
For exmaple, if the user wants to see all the data from January 2010 and they are given 2 date controls, they will choose 1/1/2010 as the start date and 1/31/2010 as the end date.
Before plugging those values into your query, you need to adjust the end date by adding one day so your query will look this:
WHERE
DateField >= Convert(DateTime, '1/1/2010')
and
DateField < Convert(DateTime, '2/1/2010')
Of course, your not actually building the sql inline, your passing the values to your stored procedure (that's another blog post)
So you have the choice of putting the 'add one day' logic into your stored procedure and passing the values directly from the user or puting the 'add one day' logic in your application code.
If your application is set up to give a single date to the user then you will have to convert that into 2 dates, the date they entered and one day latter and then build the sql with those 2 values.
Kapish?
Of course this is not the only way to go about working with dates in Sql Server but one good solution is enough...right?