Drop Time in DateTime

问题:

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.

回答1:

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

回答2:

You can use this syntax:-

CAST(date_field AS DATE) as column_name

回答3:

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                          

回答4:

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

回答5:

Here's one way:

declare @d datetime
set @d = getdate()

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

回答6:

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

回答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?

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

回答9:

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);
    }
};

回答10:

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.

回答11:

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

回答12:

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

回答13:

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

回答14:

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

回答15:

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.

回答16:

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