Take the 2-minute tour ×

I have a problem where my table stores the date as

2009-01-10 10:00:00.000

and I have another table which stores the date as

2009-01-10 12:00:00.000

I know they are not the same but the dates are, is there away in SQL to easily drop the time and keep just the dates for comparison? Thanks.

share|improve this question
What database are you running? I believe the answer varies according to DB. –  Joe Soul-bringer Jan 29 '09 at 20:42

16 Answers 16

Run this

SELECT DATEADD(dd, DATEDIFF(d, 0, Getdate()), 0)

change Getdate() to your column name to strip the date

BTW if you are doing a comparison it is better to do >= and < since it will perform better

share|improve this answer
Perform better as opposed to what '='? –  Oliver S Jan 29 '09 at 20:22
than running function in the where clause, take a look at blogs.lessthandot.com/index.php/DataMgmt/DataDesign/… to see what I mean –  SQLMenace Jan 29 '09 at 20:25
Of course, if Oliver is just looking to compare the two dates, this is actually quite inefficient. –  Mark Brittingham Jan 29 '09 at 21:40
From our own testing, this is the fasted way to make a datetime into a date. But, yes, it does prevent the benefits of indexes. –  MatBailie Jan 29 '09 at 22:01

Look here

You want the first one.


Do not use any conversions to varchar, they are slow.

EDIT: @feihtthief - Here is a way to populate a table of dates (with 0 time) from a temp numbers table that is FAST. Edit it to save the numbers table if you want. They are handy as well.

DECLARE @DaysFromGoLive int                                                                                 
SET @DaysFromGoLive = (SELECT (DATEDIFF(dd,'10/01/2007',GETDATE()) + 1)) /* Days from go live is the starting date of the table */

 SELECT TOP 10950 --30 years of days                                                                        
        IDENTITY(INT,1,1) as N                                                                              
   INTO #Numbers                                                                                            
   FROM Master.dbo.SysColumns sc1,                                                                          
        Master.dbo.SysColumns sc2                                                                           

CREATE TABLE [dbo].[TableOfDates](                                                                      
    [fld_date] [datetime] NOT NULL,                                                                           
 CONSTRAINT [PK_TableOfDates] PRIMARY KEY CLUSTERED                                                         
    [fld_date] ASC                                                                                            
)WITH FILLFACTOR = 99 ON [PRIMARY]                                                                          
) ON [PRIMARY]                                                                                              

INSERT INTO                                                                                                 
      DATEADD(dd,nums.n - @DaysFromGoLive,CAST(FLOOR(CAST(GETDATE() as FLOAT)) as DateTime)) as FLD_Date    
FROM #Numbers nums                                                                                          

SELECT MIN(FLD_Date) FROM dbo.TableOfDates                                                              
SELECT MAX(FLD_Date) FROM dbo.TableOfDates                                                              

share|improve this answer
In my opinion converting to float is poor practice, because a round-trip conversion to datetime is not reliable. Please see this post for more detail. –  ErikE Sep 13 '10 at 0:16
The goal here is to drop the time from a datetime. Even if the cast to float is not perfect, the time is getting dropped anyway. –  StingyJack Sep 13 '10 at 12:10
Casting to float is also not fastest! My objection about round-trip conversion to datetime not being reliable is that using it can be confuse developers who think such a conversion is "safe." Unless you commit to putting comments on EVERY usage to this effect, it seems a mistake to me. By the way, people are unlikely to see your comment replies unless you tag them as in @Emt... it was only luck I saw this. –  ErikE Oct 8 '10 at 4:13

You can use this syntax:-

CAST(date_field AS DATE) as column_name

share|improve this answer

If you're using MS SQL Server 2008 you could also change to the new DATE datatype instead of using DATETIME.

share|improve this answer

Here's one way:

declare @d datetime
set @d = getdate()

select dateadd(d, datediff(day, 0, @d), 0)
share|improve this answer

Converting to varchar is SLOW. Don't do it. The Datediff solution is fastest, and if you're comparing with a date you don't even have to dateadd (or alternately convert to datetime) since the integer will implicitly compare with a date no problem, as in WHERE SomeDate = DateDiff(d, 0, GetDate())

Mark Brittingham's suggestion on a join between two tables is pretty good. And to compare to just a single date, as suggested, use:

WHERE A.Dt >= @SomeDate AND A.Dt < @SomeDate + 1

share|improve this answer

All of these solutions (with the exception of the SQL Server 2008 datatype post) are row-based solutions. They'll work okay in small table situations, but they will churn in very large table cases. If you aren't on 2008, do you have the option of storing the date as separate integer fields for year, month, and day or as a calculation from some fixed date?

share|improve this answer
What do you mean by "row-based solutions"? As long as the form is ColumnDate >= DateDiff(Day, 0, @date) AND ColumnDate < DateDiff(Day, -1, @date) then it will perform no different from Convert(date, @date) since the calculations only have to be done once. –  ErikE Sep 13 '10 at 0:18
Note the timestamps. Your post was after mine, so it wasn't a comment on yours. It was based on ones where folks were trying to conversions of the column, rather than the variable. –  K. Brian Kelley Sep 22 '10 at 7:14

In Oracle you could do a trunc(DateTime) which gets rid of the time component. (There are variation like trunc(DateTime, 'MONTH') which gives the first date of the month.) Not sure if this is standard SQL, but I'm sure there would similar functions in other databases.

This should be alot faster than working with a conversion function.

share|improve this answer
Unfortunately, MS doesnt give us a nice simple function like Trunc() =( –  StingyJack Jan 30 '09 at 13:08
Trunc() also could be misused if put on the left side of the condition. Using >= and < are the SARGable way to do it. –  ErikE Sep 13 '10 at 0:18

A SQL CLR function like this should to it:

public partial class UserDefinedFunctions
    public static SqlDateTime StripTime(SqlDateTime date)
        return date.IsNull ? SqlDateTime.Null : new SqlDateTime(date.DayTicks, 0);
share|improve this answer

My suggestion is to decide on a standard and change one of the tables to match the other. Otherwise your application code is going to have to remember which table uses which method forever. Decide on a standard and stick with it. If it's truly always midnight in one table and always noon in the other table then the time portion of the date has no significance anyway.

share|improve this answer

There are many fine suggestions for taking the time out but if you are just looking for a way to compare days without considering the time component just do this:

Select * From SomeTable Where (DateDiff(d, FirstDate, SecondDate) = 0)

That is, use DateDiff with a "d" argument to do your compare rather than "=".

Just in case that is what you are really after...

share|improve this answer

My favorite way to only display (and compare) on the date is to use:


where getdate() can be your datetime value.

i believe it converts to the format of MM/DD/YYYY

share|improve this answer
Casting to any char type is always expensive, whereas converting from internally used float value to float to int is a lot cheaper. –  feihtthief Jan 29 '09 at 23:02
i got down voted because i didn't post the uber efficient method? wow... for the record i've used this approach on multi-million record tables without a hiccup –  Adam Jan 30 '09 at 18:17
@fei datetime data types are not stored as floats in SQL Server. They are stored as integers. If you're interested in proof please see this post for more detail. –  ErikE Sep 13 '10 at 0:22
@emtucifor: I stand corrected. –  feihtthief Oct 7 '10 at 20:32
@fei Hmmm, actually that post doesn't directly prove they're stored as integers. But this one does. –  ErikE Oct 8 '10 at 4:15

SQLMenaces suggesting is, in my opinion, the fastest way to strip times out from a date. It does also, however, make some queries chug like a pig by removing the ability to use indexes...

[A] JOIN [B] ON SQLMenace([A].datetime) = SQLMenace([B].datetime)

(Where SQLMenace() is the operation he described *grin*)

To use allow use of INDEXes on the 2nd table, it just needs a small adapation...

[A] JOIN [B] ON [A].datetime >= SQLMenace([B].datetime) AND [A].datetime < SQLMenace([B].datetime)

This should cause a scan on [B] and index lookups on [A]...

share|improve this answer

StingyJack has it right, but depending on what you want to do you might be better server to store the date as an integer if you never need the time part.

create table #datedemo(
 id         integer primary key identity(1,1) not null
,date       datetime not null
,dateint    int 

insert into #datedemo (date)
          select '2009-Jan-29 02:12:00'
union all select '2009-Jan-29 16:22:00'
union all select '2009-Jan-30 00:32:00'
union all select '2009-Jan-30 13:42:00'
union all select '2009-Jan-31 01:52:00'
union all select '2009-Feb-01 12:02:00'

update #datedemo set dateint = floor(cast(date as float))

select id,date,dateint,cast(dateint as datetime) from #datedemo
where dateint = floor(cast(cast('2009-Jan-30' as datetime)as float))

drop table #datedemo
share|improve this answer
If you want a dates table, check out the edit to my answer. –  StingyJack Jan 30 '09 at 13:00

Depending on your reason for zeroing-out the time...

For me, it's often been so I could compare two dates. In that case, you're better-off sticking to >=, <= comparisons (avoid =, <>) if you can. In this way, you can do the comparisons in your WHERE clause without losing the benefit of your index.

If you're on SQl-Server 2008, they have a DATE datatype now (that doesn't include time)

You can use some creative storage format -- for example, a smallint (half the size of a smalldatetime) of YYDDD, where DDD is the Julian date (each day of the year is numbered sequentially from 1 to 365). This is a smaller datatype, and valid for >,=,< comparisons. Drawback is having to convert external dates to this format (or visa-versa) before doing a comparrison.

I guess one last thing to consider is preventing time data from getting stored in the first place.

You can also do YEAR(), MONTY(), etc., which will return integer values. Might not be best for comparing dates, but useful for formatting, etc.

share|improve this answer


well, if the data type of the field is smalldatetime instead of datetime, you will only be able to use and update dates, the time will always be 00:00:000 no matter what are you inserting.

To avoid problems with dates I personally always use

CONVERT( smalldatetime, @myDate, 103 )

been 103 (you can see in the Help) it'a dd/mm/yyyy and works fine with me, I do use this technique because I use the British/French date format and the servers are in US, where they change it all :-D

it was the only idea that pop up into my head in that time (6 years ago) and I stuck with it.

But, you can think to, instead of adding a DateTime field, add an int and place the number of seconds since 01-01-1970, you will never have Date problems, and every time you need to get the Date, just subtract the current date those seconds.

This technique is very used in big programs cross countries to avoid problems, for example, the world known CRM application called SuperOffice

share|improve this answer
@balexandre: "smalldatetime" still allows a time component to be inserted, but using Convert() as you suggest will eliminate it, if you are consistent. I prefer the "int" datatype instead, or to even store separate year, month, and day "int"s when I often need to access them. –  Rob Williams Jan 29 '09 at 22:17

Your Answer


