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.
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
You can use this syntax:-
CAST(date_field AS DATE) as column_name
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
If you're using MS SQL Server 2008 you could also change to the new DATE datatype instead of using DATETIME.
Here's one way:
declare @d datetime
set @d = getdate()
select dateadd(d, datediff(day, 0, @d), 0)
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
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);
}
};
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?
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.
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.
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...
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
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]...
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
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.
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
