getdate()
-
I am trying to strip the time and seconds off of the getdate() results. how do i achieve this?
-
select dateadd(dd, 0, datediff(dd, 0, getdate()))
-
This does work but you shoudl for the sake of reference do this
select dateadd(dd, datediff(dd, 0, getdate()), 0)
Doing this will show you why
select dateadd(yyyy, 0, datediff(yyyy, 0, getdate()))
versus
select dateadd(yyyy, datediff(yyyy, 0, getdate()), 0)
-
I started to ask you to explain why the difference, but I just figured it out, the addition in DATEADD is not communitive. 0 + 107 != 107 + 0. When doing days (dd) however, it was, so it worked (39195 + 0 = 0 + 39195).
Thanks!
-
For a detailed analysis of the various methods see http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=243877#bm245300
For a column MyTs, the fastest method is:
CAST( CAST( ( MyTs - 0.5 ) as integer ) as datetime )
The various methods with the originators name is:
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,MyTs),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 rows 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 !!
Later in the post, there is a discussion regarding using this method in a where clause, such as
From MyTable
where CAST( CAST( ( MyTs - 0.5 ) as integer ) as datetime ) = @MyDate -- no time
This WILL not allow the use of an index on MyTs.
This solution can take advantage of an index to improve performance.
From MyTable
where MyTs between @MyDate and dateadd(ms,-3, @MyDate + 1 )
SQL = Scarcely Qualifies as a Language
-
Guys, I really don't know what the final type of the result should be but this is what I do:
select
convert(varchar(10),getdate(),110)
select
convert(varchar(10),getdate(),111)
select
convert(datetime,convert(varchar(10),getdate(),111))
Then if you still don't like the dashes:
select
replace(convert(varchar(10),getdate(),110),'-','/')
select
replace(convert(varchar(10),getdate(),111),'-','/')
-
I tested the methods you suggested under SQL Server 2005 Developer Edition using a table with about 5,000,000 rows of randomly generated datetime values in the range of 1753-01-01 00:00:00.000 through 9999-12-31 23:59:59.997 to test the speed.
This method is the fastest one that works for the entire range of possible datetime values:
dateadd(day,datediff(day,0, ,a.DATE_TIME),0)
This method is fast, but does not work for datetime values <= 1753-01-01 11:59:59.997 or when the value is 9999-12-31 23:59:59.997
cast(cast((,a.DATE_TIME -0.5) as integer ) as datetime )
This method works with all datetime values, but is is slower:
convert(datetime,floor(convert (float,a.DATE_TIME)))
This method works with all datetime values, but is is slower:
cast(cast(substring(cast(a.DATE_TIME as binary(8)),1,4) as int) as datetime)
I prefer the following method because it seems to be the fastest that always works, and it does no use any non-standard method that depends on ‘tricks’ with the internal structure of the datetime datatype:
dateadd(day,datediff(day,0, ,a.DATE_TIME),0)
set nocount on
go
declare @start datetime, @end datetime, @count int
select @start = getdate()
select
@count= count(*)
from
T_DATE_TEST a
where
a.DATE_TIME between '17530102 12:00:00.000' and '99991231 23:59:59.993' and
a.DATE <>
cast(cast((a.DATE_TIME-0.5) as integer ) as datetime )
select [Elapsed Method 1] = datediff(ms,@start,getdate()), @count
go
declare @start datetime, @end datetime, @count int
select @start = getdate()
select
@count= count(*)
from
T_DATE_TEST a
where
a.DATE <>
dateadd(day,datediff(day,0,a.DATE_TIME),0)
select [Elapsed Method 2] = datediff(ms,@start,getdate()), @count
go
go
declare @start datetime, @end datetime, @count int
select @start = getdate()
select
@count= count(*)
from
T_DATE_TEST a
where
a.DATE <>
convert(datetime,floor(convert (float,a.DATE_TIME)))
select [Elapsed Method 3] = datediff(ms,@start,getdate()), @count
go
declare @start datetime, @end datetime, @count int
select @start = getdate()
select
@count= count(*)
--a.DATE_TIME,
--cast(cast(substring(cast(a.DATE_TIME as binary(4)),1,2) as int) as datetime)
from
T_DATE_TEST a
where
a.DATE <>
cast(cast(substring(cast(a.DATE_TIME as binary(8)),1,4) as int) as datetime)
select [Elapsed Method 4] = datediff(ms,@start,getdate()), @count
go
Results:
-- Method 1 was slower because of the need to filter out dates it cannot handle
Elapsed Method 1
---------------- -----------
3593 0
Elapsed Method 2
---------------- -----------
1876 0
Elapsed Method 3
---------------- -----------
2216 0
Elapsed Method 4
---------------- -----------
2686 0
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply