May 22, 2007 at 9:17 am
How do you make queries on datatime fields with out the time part slipping in to trip you up? Oracle has a Trunc() function. Is there any thing like that in t-sql?
May 22, 2007 at 10:20 am
If you want to strip out the time, the fastest way (supposedly) is to do something like this...
DATEADD(dd, CONVERT(FLOAT, GETDATE()), 0)
I think that's what you're getting at...
May 23, 2007 at 7:52 am
Somebody else on this list posted the original formula to accomplish that, but I kept forgetting the syntax so I created a UDF like this:
CREATE FUNCTION [dbo].[fnNoTime]
(@DateTime datetime)
RETURNS datetime
AS
BEGIN
DECLARE @date datetime
SET @date = DATEADD(d,DATEDIFF(d,0,@DateTime),0)
RETURN @date
END
Usage: dbo.fnNoTime(some_datetime_field)
May 24, 2007 at 7:22 am
--this removes the time too - setting a variable:
declare @date datetime
set @date = convert(char(10),getdate(),101)
-- compare dates only
select * from mytable where
convert(char(10),mytabledate,101) =
convert(char(10),getdate(),101)
May 24, 2007 at 7:38 am
I did a comparison a while back, comparing the cast as varchar with the dateadd method for removing a time from a datetime, and the dateadd came in much cheaper i.t.o. cpu
I generally prefer to use between to compare dates as functions on the column will prevent index usage.
select * from tbl
where datecol between dbo.DateOnly(getdate()) and dateadd(ms,-3,dateadd(dd,1,dbo.DateOnly(getdate())))
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2007 at 10:56 am
I generally prefer to use between to compare dates as functions on the column will prevent index usage.
This is true but I rarely see only the date field in the WHERE clause. Usually it is in the form of:
WHERE SomeField = something
and SomeOtherField = somethingelse
and YetAnother between this and that
and dbo.IsInSameDay( DateField, @DateOfConcern)
As long as any one of the other fields is indexed, you'll get your seek.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
May 24, 2007 at 11:12 am
What would be the benefit of creating an index on a date field?
It seems to me that the time portion of either smalldatetime or datetime would tend to generate many unique values.
May 25, 2007 at 12:18 am
Indexes can be used for range scans.
If, say, you're always looking for the latest 10 records in the table, an index on the date field is very useful.
This is true but I rarely see only the date field in the WHERE clause.
Depends on the app. I have a lot of historical tables and queries on those often require all the records for a particular day
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply