SQL Drop Time in DateTime

Question

Hi, 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.

Answer 1

Look here

You want the first one.

SELECT (CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime))

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                                                                                                 
      dbo.TableOfDates                                                                                  
SELECT                                                                                                      
      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                                                              

DROP TABLE #Numbers

Answer 2

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

Answer 3

Here's one way:

declare @d datetime
set @d = getdate()

select dateadd(d, datediff(day, 0, @d), 0)

Answer 4

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

Answer 5

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.

Answer 6

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...

Answer 7

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?

Answer 8

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.

Answer 9

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

convert(char(10),getdate(),101)

where getdate() can be your datetime value.

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

Answer 10

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

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

Answer 11

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]...

Answer 12

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

Answer 13

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

Answer 14

A SQL CLR function like this should to it:

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDateTime StripTime(SqlDateTime date)
    {
        return date.IsNull ? SqlDateTime.Null : new SqlDateTime(date.DayTicks, 0);
    }
};