just the date, please
-
I want to extract only the date portion of a datetime field and load it into the datetime field of another table. I can do it with this...
INSERT INTO table1 ( change_date )
SELECT CONVERT( varchar(10), change_date, 111 )
FROM table2
It just seems like there should be an easier/better method. There are various cases where we are simply uninterested in the time portion of the datetime field. I wish there were just a date type field.
Suggestions please?
-
The dateadd/datediff method show below is the best way to do it.
insert into table1 ( change_date ) select dateadd(dd,datediff(dd,0,change_date),0) from table2
-
MVJ, thanks much.
Why is that best? Is it best because it is not converting it to a varchar?
-
Some say it is faster than converting to/from varchar. I have done some testing and it is sometimes faster but sometimes slower.
-
It is simple, it's much faster than converting to varchar, and it doesn't have any dependencies on the setting of DATEFORMAT or LANGUAGE.
-
I have done extensive testing against tables with millions of rows, and it is faster.
-
Michael and Lynn, thanks much for your feedback. I really appreciate it.
-
I'm not sure how different it is (haven't tested it yet), but I recall reading an article a few years back that said that this method was actually the fastest:
DATEADD(dd, CONVERT(FLOAT, mydate), 0)
My memory might be a bit hazy though.
-
Check out Frank Kalis site. He has done extensive testing on this subject and my memory seems to want to confirm that your memory is right
. -
I guess it really comes down to the volume of data you have to process and your requirements. It's always good to know different ways of accomplishing a task, so you can recognize what is going on when you find yourself in a new environment.
-
The way I do it is
select Left(GetDate(),11)
simply because it's less typing
Dave J
-
Run that with a few date formats and see how consistant the results are. They are never gonna be the same from one time to the next.
-
I don't doubt it, but I did use a smiley
Dave J
-
It was long topic here about it, and we did plenty of exercises there.
All methods involving conversions were slower.
Method using conversion to FLOAT was in my library before that, and I wiped it out. No need to use "second best" one.
_____________
Code for TallyGenerator -
I think these were part of the exercises... haven't tested all the methods for which way they round mostly because some aren't worth the effort... the following code is a self-standing speed test of various methods of producing "date with no time"... If you find something that "truncates" and is faster than "Rounding Method 2" below... be sure to let everyone know by posting your method
Note that although "Johnathons's Integer function" appears to be the fastest, it rounds up instead of truncating so I don't include it as a method I'd use for "date only"... it's just included to "wet the appetite" 
--DROP TABLE BigTest
--===== Create a test table
SELECT TOP 1000000
IDENTITY(INT,1,1) AS RowNum,
CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME) AS ADate
INTO dbo.BigTest
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== A table is not properly formed unless a Primary Key has been assigned
ALTER TABLE dbo.BigTest
ADD PRIMARY KEY CLUSTERED (RowNum)
--===== Declare a couple of operating variables for the test
DECLARE @MyDate DATETIME --Holds the result of a conversion to bypass display times
DECLARE @StartTime DATETIME --For measuring duration of each snippet
PRINT '===== Rounding method 2 ======'
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET @StartTime = GETDATE()
SELECT @MyDate=CAST(CAST((ADate - 0.5 ) AS INTEGER) AS DATETIME) FROM dbo.BigTest
PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'
PRINT ' '
PRINT '===== DateDiff/DateAdd method ======'
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET @StartTime = GETDATE()
SELECT @MyDate=DATEADD(dd,DATEDIFF(dd,0,ADate),0) FROM dbo.BigTest
PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'
PRINT ' '
PRINT '===== Rounding method 1 ======'
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET @StartTime = GETDATE()
SELECT @MyDate=CAST(ROUND(CAST(ADate AS FLOAT),0,1) AS DATETIME) FROM dbo.BigTest
PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'
PRINT ' '
PRINT '===== Convert method ================'
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET @StartTime = GETDATE()
SELECT @MyDate=CAST(CONVERT(CHAR(8),ADate,112) AS DATETIME) FROM dbo.BigTest
PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'
PRINT ' '
PRINT '===== Johnathons''s Integer function ====='
-- Does NOT truncate... does a round be careful!!!!
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET @StartTime = GETDATE()
SELECT @MyDate=CAST(convert(int, convert(float, Adate)) AS DATETIME) FROM dbo.BigTest
PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'
PRINT ' '
PRINT '===== Floor method ====='
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET @StartTime = GETDATE()
SELECT @MyDate=CAST(FLOOR(convert(float, Adate)) AS DATETIME) FROM dbo.BigTest
PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'
PRINT ' '
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 62 total)
You must be logged in to reply to this topic. Login to reply