May 20, 2008 at 1:36 am
gaurava16fc (5/20/2008)
select count(datetime) from XYZ where convert(datetime,left((convert(nvarchar,datetime),11)) = '2008-05-15'
Yes, that works... but no chance of an Index SEEK. The code should be written similar to the following to allow an index to work properly...
[font="Courier New"]
SELECT COUNT(DateTime
FROM yourtable
WHERE datecolumn >= '2008-05-15'
AND datecolumn < '2008-05-16'[/font]
--Jeff Moden
May 21, 2008 at 9:38 am
We've got a UDF called TruncDate that effective truncates the date by removing the time element from it. This mimics the TRUNC() command in Oracle.
ALTER FUNCTION TruncDate
(
@InDate DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @ResultVar DATETIME
SELECT @ResultVar = CAST((CAST(DATEPART(YEAR, @InDate )AS VARCHAR)
+ '-'
+ REPLICATE('0',2-LEN(MONTH(@InDate)))
+ CAST(DATEPART(MONTH, @InDate) AS VARCHAR)
+ '-'
+ CAST(DATEPART(DAY, @InDate) AS VARCHAR)
) AS DATETIME
)
RETURN @ResultVar
END
GO
May 21, 2008 at 9:42 am
What did you mean by this?
--===== Gives the correct answer but can't use an index properly.
-- Will only do an Index SCAN, no chance of Index SEEK.
-- Should be corrected to use ISO date literal, as well
PRINT 'Gives the correct answer but can''t use an index properly.'
SELECT *
FROM #TestTable
WHERE CONVERT(VARCHAR(10),MyDate,101)='05/15/2008'
I am just hearing about indexes being affected by functions. . .
May 21, 2008 at 9:48 am
tony.sawyer (5/21/2008)
We've got a UDF called TruncDate that effective truncates the date by removing the time element from it. This mimics the TRUNC() command in Oracle.
Why do all of that CASTing to characters and then recasting back to DATETIME? It is not very efficient. Better code:
RETURN DATEADD(day, DATEDIFF(day, 0, @InDate), 0)
May 21, 2008 at 9:56 am
JohnG (5/21/2008)
tony.sawyer (5/21/2008)
We've got a UDF called TruncDate that effective truncates the date by removing the time element from it. This mimics the TRUNC() command in Oracle.Why do all of that CASTing to characters and then recasting back to DATETIME? It is not very efficient. Better code:
RETURN DATEADD(day, DATEDIFF(day, 0, @InDate), 0)
Cool, thats a much more concise technique than the one we were using, I'll have to update our system
Thanks John
May 21, 2008 at 9:56 am
tony.sawyer (5/21/2008)
We've got a UDF called TruncDate that effective truncates the date by removing the time element from it. This mimics the TRUNC() command in Oracle.
ALTER FUNCTION TruncDate
(
@InDate DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @ResultVar DATETIME
SELECT @ResultVar = CAST((CAST(DATEPART(YEAR, @InDate )AS VARCHAR)
+ '-'
+ REPLICATE('0',2-LEN(MONTH(@InDate)))
+ CAST(DATEPART(MONTH, @InDate) AS VARCHAR)
+ '-'
+ CAST(DATEPART(DAY, @InDate) AS VARCHAR)
) AS DATETIME
)
RETURN @ResultVar
END
GO
A simplier way without a lot of CASTing and concatenation:
ALTER FUNCTION TruncDate
(
@InDate DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @ResultVar DATETIME
SET @ResultVar = dateadd(dd, datediff(dd,0,@InDate), 0)
RETURN @ResultVar
END
GO
😎
May 21, 2008 at 11:11 am
Keep in mind that an inline function (like these) has an overhead to it. With something as simple as the dateadd version for this, you're better off including it in the query, not calling a function.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 11:25 am
GSquared (5/21/2008)
Keep in mind that an inline function (like these) has an overhead to it. With something as simple as the dateadd version for this, you're better off including it in the query, not calling a function.
Couldn't agree more.
😎
May 21, 2008 at 12:10 pm
I find it interesting how many different ways we can find to solve a problem using T-SQL. Here is another way to strip-off the time and just return the date:
SELECT CAST(CAST(CAST(GETDATE() AS FLOAT) AS INT) AS DATETIME);
This is based on the fact that DATETIME values are stored as a decimal value with the date to the left of the decimal and the time to the right of the decimal.
May 22, 2008 at 1:53 am
Hi All,
Thanks for the advice, it is really appreciated.
🙂
May 22, 2008 at 8:46 am
Paul Lach (5/21/2008)
Here is another way to strip-off the time and just return the date:SELECT CAST(CAST(CAST(GETDATE() AS FLOAT) AS INT) AS DATETIME);
This is based on the fact that DATETIME values are stored as a decimal value with the date to the left of the decimal and the time to the right of the decimal.
Although this will work (today) it is based upon knowledge of how SQL Server internally stores the date and time. It may not work in a future version. This could break if Microsoft changed the underlying internal storage format. Proper coding would dictate that you use the bulit-in functions such as DATEADD, DATEDIFF, etc. that operate on the native data type.
May 23, 2008 at 9:13 am
The issue with the milliseconds would be an excellent Question of the Day!
May 23, 2008 at 9:16 am
Even simpler
ALTER FUNCTION TruncDate
(
@InDate DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEDIFF(DAY, '19000101', @InDate)
END
N 56°04'39.16"
E 12°55'05.25"
May 23, 2008 at 5:24 pm
Peso (5/23/2008)
Even simpler
ALTER FUNCTION TruncDate
(
@InDate DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEDIFF(DAY, '19000101', @InDate)
END
Simple... yes. Clever... very. Expensive... YES!
In SQL Server 2000 sp3a, the function takes 00:01:18.xxx to process a million dates whereas the following code takes less than a second.
DECLARE @Bitbucket DATETIME
SELECT @Bitbucket = DATEADD(dd,DATEDIFF(dd,0,SomeDate),0) --0:01
FROM dbo.JBMTest
It's not quite as bad in 2k5 sp2... the function only takes 00:00:06.750... but the direct code only takes 00:00:00.907. Both are the CPU times shown in profiler.
The real bad part about this is that the following code shows identical execution plans and that both supposedly take 50% of the run... which, of course, is grossly incorrect.
--===== Direct method to truncate date
DECLARE @Bitbucket DATETIME
SELECT @Bitbucket = DATEADD(dd,DATEDIFF(dd,0,SomeDate),0)
FROM dbo.JBMTest
GO
--===== Function to truncate date
DECLARE @Bitbucket DATETIME
SELECT @Bitbucket = dbo.TruncDate(SomeDate)
FROM dbo.JBMTest
GO
My recommendation is to teach developers how to do the direct method... and delete the function from your database.
--Jeff Moden
May 25, 2008 at 10:17 pm
GSquared (5/16/2008)
Don't use Between for date ranges. Use:
Where date >= '5/16/2008' and date < '5/17/2008'
Why?
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply