How to compare the date only (without timestamp) in an SQL Select statement

Question

I have an SQL SELECT statement to select records from my table based on the current date. What I want to do is compare the dates without the timestamps because the times do not match. I just care for the date.

Here is what I have:

string SQL = @"SELECT Trans_Set_Id FROM EDI10002 " +
             @"WHERE (Tp_Id = '" + tpid + "' AND Rec_Date = '" + DateTime.Now.ToShortDateString() + @"')";

The issue I'm having is how do I get the date only from the field "Rec_Date"?

As a side note, can you guarantee that tpid is always an integer? (I assume so) [Sql Injection](http://xkcd.com/327/) is bad — Dan Pichelman, Mar 08 '13 at 18:02

score 4 · Answer 1 · answered Mar 08 '13 at 17:26

4

If you're using sql server 2008 and above, you could do this:

select cast( getdate() as date)

answered Mar 08 '13 at 17:26

AdamL

12,421
5
50
74

would I use the cast function on my "Rec_Date" field? Where would I insert my field? – hmakled Mar 08 '13 at 17:31
@Grant - I got error msg: Type DATE is not a defined system type. Wow whats going on. – hmakled Mar 08 '13 at 17:43
1

What is the output of `select @@version`? The DATE type hasn't been around forever – Dan Pichelman Mar 08 '13 at 17:59

score 2 · Answer 2 · answered Mar 08 '13 at 17:27

2

What Database are you using?

Have a look at the DATEPART function for mssql http://msdn.microsoft.com/en-us/library/ms174420.aspx

answered Mar 08 '13 at 17:27

Jras

518
3
12

Take a look at this link, I think it has what you are looking for http://stackoverflow.com/questions/1503298/sql-statement-to-select-all-rows-from-previous-day – Jras Mar 08 '13 at 18:03

RandomUs1r · Accepted Answer · 2013-03-08T18:20:25.307

2

A slightly old-fashioned way to do it is:

string SQL = "SELECT Trans_Set_Id FROM EDI10002 WHERE (Tp_Id = '" + tpid + "' AND CONVERT(varchar(10),Rec_Date, 101) = CONVERT(varchar(10), " + DateTime.Now.ToShortDateString() + ", 101))";

you're basically converting to MM/dd/YYYY and then truncating off the end.

Edit: With getdate()

string SQL = "SELECT Trans_Set_Id FROM EDI10002 WHERE (Tp_Id = '" + tpid + "' AND CONVERT(varchar(10),Rec_Date, 101) = CONVERT(varchar(10), getdate(), 101))";

edited Mar 08 '13 at 18:20

answered Mar 08 '13 at 17:31

RandomUs1r

4,010
1
24
44

I tried that and got an error msg: The name 'CONVERT' does not exist in the current context – hmakled Mar 08 '13 at 17:38
what version of SQL are you using? – RandomUs1r Mar 08 '13 at 17:40
I had a simple typo, try it now. – RandomUs1r Mar 08 '13 at 17:42
Still getting the same error. For some reason its not liking the CONVERT function on the right side of the equation. – hmakled Mar 08 '13 at 17:51
didn't notice that was a vb function, try again, why not just use getdate()? – RandomUs1r Mar 08 '13 at 17:53
I tried using getdate as well, but I get an error saying: Type DATE is not a defined system type. Any Ideas? – hmakled Mar 08 '13 at 18:17
see edit, also removed the @, no need for that here. Either example should work from within .net code though. – RandomUs1r Mar 08 '13 at 18:21

Grant Winney · Answer 4 · 2013-03-08T17:29:50.330

1

Try using TRUNC(Rec_Date) if you only care about the date portion and want to drop the time portion. Something like:

... "' AND TRUNC(Rec_Date) = '" + DateTime.Today...

(This worked in Oracle... it may just be an Oracle thing...)

edited Mar 08 '13 at 17:29

answered Mar 08 '13 at 17:24

Grant Winney

65,241
13
115
165

When I do that i get an error saying "TRUNC is not a recognized built-in function name" – hmakled Mar 08 '13 at 17:27

score 1 · Answer 5 · answered Mar 08 '13 at 18:35

The following should work in all versions of SQL Server, regardless of the type of Rec_Date:

SELECT Trans_Set_Id 
FROM EDI10002 
WHERE Tp_Id = @tpid 
      AND (Rec_Date >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
           AND Rec_Date < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1))

The use of DATEADD(day, DATEDIFF(...), ...) gets the beginning of a specific day. This means that if you have an index defined on Rec_Date, it may be used to retrieve the requested rows.

score 1 · Answer 6 · answered Mar 09 '13 at 03:29

First you want to get out of the habit of building dynamic sql statements using string concatenation, you want to use parameterized queries.

Secondly in your example, you don't need to supply the date from the client, it can all be done in sql.

string SQL = @"SELECT Trans_Set_Id FROM EDI10002 " +
   @"WHERE (Tp_Id = @tpid" + 
   @" AND convert(varchar(10, Rec_Date, 121) = CONVERT(varchar(10), getdate(), 121) )";

or if you're using 2008

string SQL = @"SELECT Trans_Set_Id FROM EDI10002 " +
   @"WHERE (Tp_Id = @tpid" + 
   @" AND cast(Rec_Date as date) = cast(getdate() as date) )";

Add @tpid as a paremter to your sql command, and you're done.

How to compare the date only (without timestamp) in an SQL Select statement

6 Answers6