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: How to remove the time portion of a datetime value (SQL Server)?

if you BETWEENs don't work because the datetime has time other than 00:00:00.000, fix the BETWEENS, not the data! — KM., Mar 27 '09 at 17:04

Tomalak · Accepted Answer · 2009-03-27T09:14:48.497

7

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.

edited Mar 27 '09 at 09:14

answered Mar 27 '09 at 09:07

Tomalak

332,285
67
532
628

1

@Sem The casting way is NOT faster. Please see [this post for more detail](http://stackoverflow.com/questions/2775/whats-the-best-way-to-remove-the-time-portion-of-a-datetime-value-sql-server/3696991#3696991). – ErikE Sep 13 '10 at 00:05
@Sem For those lucky enough to be off SQL 2005 or even SQL 2000... :) – ErikE Sep 13 '10 at 06:54
@Tomalak I don't think an implicit cast on a literal is any slower than an explicit cast. – ErikE Sep 13 '10 at 07:32

score 1 · Answer 2 · answered Mar 27 '09 at 08:32

1

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

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

answered Mar 27 '09 at 08:32

TechTravelThink

3,014
3
20
13

score 1 · Answer 3 · answered Mar 27 '09 at 09:21

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

score 0 · Answer 4 · edited Jul 12 '12 at 11:18

0

SELECT CONVERT(DATETIME, CONVERT(DATE, GETDATE()))

edited Jul 12 '12 at 11:18

Druid

6,423
4
41
56

answered Jul 12 '12 at 10:48

mohan

1

What does it adds to current answers? – Yaroslav Oct 10 '12 at 20:26

score 0 · Answer 5 · answered Apr 11 '16 at 12:57

0

floor today date and time is simple as shown below:

select DATEADD(dd, 0, DATEDIFF(dd, 0, getdate()))

answered Apr 11 '16 at 12:57

Qasim Bataineh

687
6
3

score 0 · Answer 6 · answered Mar 27 '09 at 08:28

0

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.

answered Mar 27 '09 at 08:28

Element

3,981
7
42
51

You don't need to write any complex code to do this - simply cast(DateTimeValue as int). – Ed Harper Mar 27 '09 at 08:34

score 0 · Answer 7 · answered Mar 27 '09 at 08:35

0

If you're working in .NET you can use

DateTime.Now.Date

to give you just the date part of a datetime.

answered Mar 27 '09 at 08:35

Neil Barnwell

41,080
29
148
220

Yes that's fair enough. I did make a big assumption that this might be suitable. – Neil Barnwell Mar 27 '09 at 10:53

score 0 · Answer 8 · edited Sep 13 '10 at 06:56

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

Save DateTime in mssql 2005 without hours, minutes and seconds

8 Answers8

Linked