Get row where datetime column = today

Question

In sql 2005, instead of building a query from dateparts year, month and date,

is there an more succinct way of writing the where clause?

I'm wondering whether any of the answers are anymore succinct than this. I find this more readable too. where (DATEPART(yy, myColum) = '2010') AND (DATEPART(mm, myColum) = '4') AND (DATEPART(dd, myColum) = '7'); — Chin, Apr 06 '10 at 09:37
**@Chin, your method will prevent index usage, and will result in a slow table scan.** I would highly recommend that you NEVER search based on a date using that method. You need to "floor" the dates in the range, and apply no functions to the column if you want to use an index. this is how to floor a datetime: http://stackoverflow.com/questions/85373/floor-a-date-in-sql-server — KM., Apr 06 '10 at 11:43
@KM, thanks for that. I thought I'd ask and see whether I was off base. Currently I'm unaware of an index on this column. I'll keep this in mind though and floor my dates as you have suggested. — Chin, Apr 07 '10 at 07:11
possible duplicate of [How to return the date part only from a SQL Server datetime datatype](http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype) — Akira Yamamoto, May 23 '14 at 18:58

marc_s · Answer 1 · 2010-04-06T07:24:11.853

35

On SQL Server 2008, you would have a new DATE data type, which you could use to achieve this:

SELECT (list of fields)
FROM dbo.YourTable
WHERE dateValue BETWEEN 
   CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE))

The CAST(GETDATE() AS DATE) casts the current date and time to a date-only value, e.g. return '2010-04-06' for April 6, 2010. Adding one day to that basically selects all datetime values of today.

In SQL Server 2005, there's no easy way to do this - the most elegant solution I found here is using numeric manipulation of the DATETIME to achieve the same result:

SELECT (list of fields)
FROM dbo.YourTable
WHERE dateValue BETWEEN 
   CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) AND 
   DATEADD(DAY, 1, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))

edited Apr 06 '10 at 07:24

answered Apr 06 '10 at 07:17

marc_s

732,580
175
1,330
1,459

@marc_s - did you just delete your answer? freaked me out as I was trying to add a comment. If only I cud upgrade. Thanks for your help though much appreciated. – Chin Apr 06 '10 at 07:25
@Chin: yes, I had a silly answer first, deleted it, edited it, and undeleted it again :-) – marc_s Apr 06 '10 at 07:27
@marc_s: You sure have a deranged definition of the word "elegant", +1 :) – Jørn Schou-Rode Apr 06 '10 at 07:28
OK - I think PostMan's way to extracting only the date-part of a DATETIME is probably even more elegant ;-) – marc_s Apr 06 '10 at 07:30
@marc_s Thanks for this, How do you think this would perform in comparison with using 3 datepart clauses? – Chin Apr 06 '10 at 08:46
1

@Chin: if you have an index on the datetime field, it should be fairly OK in terms of performance. If you add the three parts Year,Month,Day to your table as computed fields and put an index on those three fields, it would be even better since it only needs to do an equality match (Year=2010,Month=04,Day=06) rather than a range query. – marc_s Apr 06 '10 at 09:06
Gotcha, this has been very insightful. – Chin Apr 06 '10 at 11:07

PostMan · Accepted Answer · 2010-04-06T07:34:24.273

26

In SQL 2000 and SQL 2005 you can use a nice select statement to remove the time component from a DateTime, ie

SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)

will return 6-Apr-2010 (well for today only).

So combined with marc_s's answer, you'd want

SELECT (list of fields)
FROM dbo.YourTable
WHERE dateValue BETWEEN DATEADD(dd, DATEDIFF(dd,0,'MY Date and Time, But I Only Want Date'), 0) 
AND DATEADD(dd, DATEDIFF(dd,0,'MY Date and Time, But I Only Want Date'), 1)

Edit: Changed to suit requirements, note the 1 in the second DateAdd (this adds the days it has been from the beginning to 1 (instead of 0), making it 7th Apr 2010 00:00:00) If you want 6th Apr 23:59:59 you take a second away from the second date

DATEADD(ss,-1,'My DateTime')

Final Call would become

DATEADD(ss,-1,DATEADD(dd, DATEDIFF(dd,0,'MY Date and Time, But I Only Want Date'), 1))

Ok that's alot of info at once! Hope it all makes sense :)

edited Apr 06 '10 at 07:34

answered Apr 06 '10 at 07:24

PostMan

6,899
1
43
51

I like your expression to get the date-only part better than my solution - but this select won't work, no?? If you have '2010-04-06 15:15:15' - how is comparing that to just the date part going to select that?? I think you need a `WHERE dateValue BETWEEN (today) AND (today+1 day)` kind of select to grab all those values today with any time-portion attached to them.... – marc_s Apr 06 '10 at 07:29
1

If you want to completely destroy the indexes, you could use the DateAdd/DatePart on the DateValue, but that's a bad idea! (If you have good indexes :) ) – PostMan Apr 06 '10 at 07:40
I have had times that are between 23:59:59.000 and 00:00:00.000 the next day eliminated from reports. so I would never use the "subtract one second method". It is better to keep the time at 00:00:000.000 and increment one day and just use "<" less than (yourColumn < FlooredEndRangeDateTime+1) – KM. Apr 06 '10 at 11:42

score 5 · Answer 3 · edited Jul 29 '13 at 09:41

5

Not good performance, but a solution:

SELECT * 
FROM tblDate
WHERE CONVERT(VARCHAR, date, 112) = CONVERT(VARCHAR, GETDATE(), 112);

If it is a common query a computed column should be added with only year-month-day content.

edited Jul 29 '13 at 09:41

Bakudan

19,134
9
53
73

answered Apr 06 '10 at 07:27

Joakim Backman

1,875
13
12

you are correct, it will be slow, because no index will be used – KM. Apr 06 '10 at 11:37

score 1 · Answer 4 · edited Jul 29 '13 at 09:42

1

Something along the lines of this

CONVERT(date,tbldata.date)=CONVERT(date,getdate())

This assumes that the date column includes the time as well. If it does not then you can change it to

tbldata.date=CONVERT(date,getdate())

edited Jul 29 '13 at 09:42

Bakudan

19,134
9
53
73

answered Apr 06 '10 at 07:23

Kevin Ross

7,185
2
21
27

Sql Server 2005 has a `date` type? ;) – Jørn Schou-Rode Apr 06 '10 at 07:26
Turns out it does not. My current stack is SQL server 2008 its been a while since we upgraded and I assumed that 2005 had this feature. – Kevin Ross Apr 06 '10 at 08:05

score 0 · Answer 5 · answered Apr 06 '10 at 07:46

0

I've used a UDF to do this on datetime columns going back to SQL 2000.

CREATE FUNCTION [dbo].[GETDATENOTIME](@now datetime)
RETURNS smalldatetime
AS
BEGIN
    RETURN (CONVERT(smalldatetime, CONVERT(varchar, @now, 112)))
END

Usage:

DECLARE @date smalldatetime
SET @date = '4/1/10'
SELECT * FROM mytable WHERE dbo.GETDATENOTIME(date) = @date

Not the most efficient thing in the world (YMMV depending on your table) but it does the job. For tables where I know I'll be selecting based on date-without-time a lot, I'll have a column specifically for that with a default set to dbo.GETDATENOTIME(GETDATE()).

answered Apr 06 '10 at 07:46

Dane

9,737
5
28
23

1

if you ever had an index on the "date" column, this will definitely not be able to use it and thus result in a table scan.... – marc_s Apr 06 '10 at 08:09
Hence my disclaimer at the end. The OP asked for succinct syntax, so that's the answer I gave him. – Dane Apr 06 '10 at 17:55

Get row where datetime column = today

5 Answers5

Linked

Related