Is there a better way to ignore time portion of a smalldatetime field
-
select convert(smalldatetime, (convert(varchar(10), dateField, 101))) as dateField
from dbo.TableA
-
No thats basically it.
If you find yourself using that alot, you can certainly create a user defined function. and call it, instead of typing the convert statement a bunch of times.
-
Actually, that's not the most efficient way. For a smalldatetime, it is stored internally as two 2-byte integers.
You can convert a date to a float, Floor() it to get the 2 bytes that represent the date, then convert that back to a smalldatetime to get the date without time:
Declare @test-2 As SmallDateTime
Select @test-2 = '13 Dec 2005 13:51'
Select convert(smalldatetime, floor(convert (float, @test-2 )))
This saves the string-parsing required to extract date parts from a Varchar(10) allowing for locale & date format etc.
Adding to that, if the expression is being used to filter records, and if the smalldatetime column is indexed, the use of the expression with prevent indexes from being used. Try this example, and run query showplan on the 2 selects. 1 will tablescan, 1 will use an index seek. Huge performance difference if this is a large data set.
-- Test temp table
create table #test
(
DataColumn varchar(50) not null,
DateTimeStamp smalldatetime not null
)
-- Create some test data over a 5 day period
Insert Into #test (DataColumn, DateTimeStamp) values ('blah blah', DateAdd(d, -4, getdate()) )
Insert Into #test (DataColumn, DateTimeStamp) values ('blah blah2', DateAdd(d, -3, getdate()) )
Insert Into #test (DataColumn, DateTimeStamp) values ('blah blah3', DateAdd(d, -2, getdate()) )
Insert Into #test (DataColumn, DateTimeStamp) values ('blah blah4', DateAdd(d, -1, getdate()) )
Insert Into #test (DataColumn, DateTimeStamp) values ('blah blah5', getdate() )
--Index the smalldatetime column
Create Unique Index #ixTestDateTime on #test(DateTimeStamp)
-- Filter records using an expression to remove time portion
Select * from #test
Where convert(smalldatetime, (convert(varchar(10), DateTimeStamp, 101))) = '13 Dec 2005'
-- Filter records by date range to avoid the expression
Select * from #test
Where DateTimeStamp >= '13 Dec 2005' And DateTimeStamp < '14 Dec 2005'
-
There are several threads discussing this subject and I think the concensus is that the following are the most efficient to avoid the use of CONVERT.
CAST(DATEADD(day,DATEDIFF(day,0,@dateField),0) as smalldatetime)
CAST(CAST(@dateField as int) as smalldatetime)
But as PW has stated, this will not help if you are matching against an indexed column. I do as shown in PW's last query but using variables, eg
DECLARE @FromDate smalldatetime,@ToDate smalldatetime
SET @FromDate = '20051213'
SET @ToDate = DATEADD(day,1,@FromDate)
SELECT DateTimeStamp
FROM
WHERE DateTimeStamp >= @FromDate
AND DateTimeStamp <= @ToDate
Far away is close at hand in the images of elsewhere.
Anon. -
USE the CAST function
select
SQLServerName = substring(a.Name,1,20)
,' date' = CAST ( a.createdate as varchar(11))
From dbo.table1 a
,dbo.table2 s
where s.Name = a.Name
group by substring(a.Name,1,20),CAST ( a.createdt as varchar(11))
Thanks,
-Nirmal
SQL Server DBA /Oracle DBA
-
hey
you can try this way its easy know..
Select convert (varchar, getdate(),101)
regards
balram
-
Just for giggles. You can also use the internal storage format of a SMALLDATETIME to get rid of the TIME portion.
DECLARE @dt SMALLDATETIME
SET @dt = CAST(GETDATE() AS SMALLDATETIME)
SELECT
CAST(CAST(SUBSTRING(CAST(@dt AS BINARY(4)),1,2) AS INT) AS SMALLDATETIME)
Though it doesn't look that intuitive, it seems to be even slightly faster than the DATEADD(DATEDIFF()) approach in most scenarios.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Try this
SELECT DATEADD(day, DATEDIFF(day, 0, getdate()), 0)
You can make it into UDF
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
I ran a quick benchmark and included Sergiy's solution to this problem.
Sergiy.......... CAST( CAST( ( MyTs - 0.5 ) as integer ) as smalldatetime )
PW............... convert(smalldatetime, floor(convert (float, MyTs )))
David Burrows... CAST(DATEADD(day,DATEDIFF(day,0,@dateField),0) as smalldatetime)
Frank Kalis..... CAST(CAST(SUBSTRING(CAST(MyTs AS BINARY(4)),1,2) AS INT) AS SMALLDATETIME)
Benmark based on running 8,640 on a Pentium 400:
...............CPU Ms....Elapsed
Sergiy..............279......279
PW...................311......372
David Burrows..301......307
Frank Kalis.......309......309
AND THE WINNER IS Sergiy !!
SQL = Scarcely Qualifies as a Language
-
LOL .. so I wasn't the only 1 having a slow Friday at work. I did some benchmarking too and came to the conclusion that ... I'd learned something new. Thanks guys, so many ways to skin a cat, some of which performed better and which I'd never seen before.
-
Sergiy.......... CAST( CAST( ( MyTs - 0.5 ) as integer ) as smalldatetime )
PW............... convert(smalldatetime, floor(convert (float, MyTs )))
David Burrows... CAST(DATEADD(day,DATEDIFF(day,0,@dateField),0) as smalldatetime)
Frank Kalis..... CAST(CAST(SUBSTRING(CAST(MyTs AS BINARY(4)),1,2) AS INT) AS SMALLDATETIME)
Benmark based on running 8,640 on a Pentium 400:
...............CPU Ms....Elapsed
Sergiy..............279......279
PW...................311......372
David Burrows..301......307
Frank Kalis.......309......309
AND THE WINNER IS Sergiy !!
Carl, how does this SELECT CAST(DATEDIFF(DAY,0,GETDATE()) AS SMALLDATETIME) perform in your test?
I'm guessing that it's among the top performers. However, would you ever use such a construct in the WHERE clause of a query? Certainly not, or?
The only drawback with Sergiy's and PW's solution I see is, that a conversion from DATETIME to FLOAT and back again is not explicitely mentioned in BOL, AFAIK. In Microsoft terms this usually means that it is an unofficial and therefore unsupported solution. And as such potentially subject to change anytime.
Anyway, there is definitely more than one way to skin that cat. And which one performs "best" in a given scenario is, as always, to be tested.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
"However, would you ever use such a construct in the WHERE clause of a query?"
I would hope not, as this would preclude the use of an index.
For the WHERE, bettter to use the between with the end date being the start date plus 1 day and then minus 3 milliseconds.
A "date only" algorithm would be useful in a check constraint to insure that the time is always midnight and also useful in a group by to aggregate by date.
SQL = Scarcely Qualifies as a Language
-
As I'm being told, MS dropped their announced new DATE data type from SQL Server 2005. What a pity!
Btw, when you are interested - for whatever reason - in the time portion, and you CAST back and forth between DATETIME and FLOAT, your chances are pretty good to produce inaccuracies every now and then
SELECT GETDATE(), CAST(CAST(GETDATE() AS FLOAT) AS DATETIME)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply