Save DateTime in mssql 2005 without hours, minutes and seconds

Question

I want to save my dates in sql2005 as dates (without hour minutes and seconds). I want to do this because the between function isn't always correct if the hours, minutes and seconds are filled in.

but neither datetime nor smalldatetime allows this, in 2008 you have the Date column which can do this.

I've also found this question which helps me out but I don't like the casting to execute my queries: http://stackoverflow.com/questions/2775/whats-the-best-way-to-remove-the-time-portion-of-a-datetime-value-sql-server

Answer 1

If all else fails you could store it as an integer value of seconds from a point in time. This would allow you to compare it against other dates stored the same way without casting.

Answer 2

SELECT CONVERT (date, GETDATE()) -- Without hours, minutes and seconds.

For more details, please refer to http://msdn.microsoft.com/en-us/library/ms188383.aspx

Answer 3

If you're working in .NET you can use

DateTime.Now.Date

to give you just the date part of a datetime.

Answer 4

The non-casting way:

SET @d = DATEADD(dd, 0, DATEDIFF(dd, 0, @dt))

This adds the number of days between 0 and your date to 0, resulting in the time-part to be zero.

If that is faster than the explicit CAST approach must be tested. The use of 0 suggests some implicit casting will be done in this expression.

Answer 5

What you could do is make sure that the dates being stored do not have minutes and seconds BEFORE you send them to the database. This should be pretty simple from your application code if you create a datetime based on an existing datetime but set the hours, minutes and seconds to zero

Answer 6

BETWEENs won't work because the datetime has time other than 00:00:00.000, so fix the BETWEENS, not the data.

This will return all data on a single day, regardless of time

DECLARE @FilterDate datetime
SET @FilterDate='3/18/2009'

QUERY
    ...
    WHERE Column1 >= @FilterDate AND Column1 < @FilterDate + 1

This will return all data on a date range, regardless of time

DECLARE @StartFilterDate datetime
DECLARE @EndFilterDate datetime
SET @StartFilterDate ='3/18/2009'
SET @EndFilterDate ='3/30/2009'

QUERY
    ...
    WHERE Column1 >= @StartFilterDate AND Column1 < @EndFilterDate + 1