excluding timestamp from sql

Question

I have a sql statement which compares dates, basically like this:

date1=date2

Now the trouble is if date 1 == 08/10/2013 12:00 and date2 == 08/10/2013 13:00 due to the timestamp being different a result set is not returned. How can I do this whilst ignoring the timestamp?

Thanks

No Mr. Bond, I expect you to convert the time stamp to dd/mm/yyyy format before comparing! — Bhrugesh Patel, Oct 08 '13 at 18:07
Based on your comments [here](http://stackoverflow.com/a/19255101/334849) implying SQL Server (even though you haven't responded to multiple requests for your RDBMS), possible duplicate of [Compare two DATETIME only by date not time in SQL Server 2008](http://stackoverflow.com/questions/1843395/compare-two-datetime-only-by-date-not-time-in-sql-server-2008), [Best way to compare dates without time in SQL Server](http://stackoverflow.com/questions/9308635/best-way-to-compare-dates-without-time-in-sql-server) and many others... — LittleBobbyTables - Au Revoir, Oct 08 '13 at 18:29

score 2 · Answer 1 · answered Oct 08 '13 at 18:07

2

In many databases this will work:

trunc(date1) = trunc(date2)

This will truncate the date/time value so only the date remains. This might work for Oracle and MySQL using date fields. But it depends on the database and on the type of the field (timestamp vs date or datetime).

answered Oct 08 '13 at 18:07

GolezTrol

114,394
18
182
210

Beat me :). So an upvote instead of my own anwer. – thursdaysgeek Oct 08 '13 at 18:09
1

Have you got a mssql equivilent, doesnt seem to work with mssql :( – James Bond Oct 08 '13 at 18:16
1

Finally, the request to know your DBMS is answered. Now, can you also share the field type? Does MSSQL have a difference between Date and Timestamp? I need to know before I can google the answer for you. :) – GolezTrol Oct 08 '13 at 19:21
See this question: [Best approach to remove time part of datetime in SQL Server](http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server) – GolezTrol Oct 08 '13 at 19:23

score 0 · Answer 2 · answered Oct 08 '13 at 18:07

0

Compare the two, but with Date(), like:

WHERE DATE(date1) = DATE(date2);

Syntax might depend on exactly what database you're using, though.

answered Oct 08 '13 at 18:07

Mike

2,132
3
20
33

score 0 · Answer 3 · answered Oct 08 '13 at 20:40

0

Another option, if you are using SQL Server

WHERE DateDiff(day, date1, date2) = 0

answered Oct 08 '13 at 20:40

Bill

4,425
3
21
22

score 0 · Answer 4 · answered Oct 08 '13 at 22:01

0

Assuming SQL 2008+

WHERE cast(date1 as date) = cast(date2 as date)

answered Oct 08 '13 at 22:01

Ben Thul

31,080
4
45
68

excluding timestamp from sql

4 Answers4