July 8, 2008 at 12:31 pm
Guys,
I have timestamp fields like '1994-08-01 00:13:00.000' from which I would like to populate only date and strip of any hh,mm,ss - for the output to be '1994-08-01 00:00:00.000'.
Is there any way to do this.
Thanks
July 8, 2008 at 12:38 pm
Try this.
DECLARE @TheDate DATETIME
SET @TheDate = '1994-08-01 00:13:00.000'
select dateadd(dd, datediff(dd,0, @TheDate),0)
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
July 8, 2008 at 12:58 pm
Thanks that works
July 9, 2008 at 5:34 am
DECLARE @MyDate datetime
SET @MyDate = '1994-08-01 00:13:00.000'
-- Converting date to varchar gives you option to convert in the
--required format and then convert back to the datetime for future
--referance as datetime. format 101 is used in this case.
Select convert(datetime,convert(varchar(10),@MyDate,101))
Hope this will help you.
Atif Sheikh
July 9, 2008 at 5:50 am
I prefer the method that Gail listed... it's all "math" and that makes if faster than character conversions. It also keeps it as a DateTime datatype so that other calculations may be done using the data without any conversions. I also avoid formatting things in SQL Server... that's the GUI's job, if there is one, so that local formatting and other settings may prevail. If you format the data at the SQL Server level, you may actually have to undo the format and reformat for a particular country's needs. 🙂
--Jeff Moden
July 9, 2008 at 5:53 am
Oh yeah... almost forgot... there is one faster method... convert to Float, distribute Floor, convert back to DateTime... but most people don't use that because it only brings a little more speed and it's a bit longer to write... including me. 😉
--Jeff Moden
July 9, 2008 at 6:26 am
Jeff Moden (7/9/2008)
Oh yeah... almost forgot... there is one faster method... convert to Float, distribute Floor, convert back to DateTime... but most people don't use that because it only brings a little more speed and it's a bit longer to write... including me. 😉
I normally use the Float/floor version in my own code, but I teach teh dateadd/datediff, because it's extendable to other beginnings (or week, of month, of quarter)
Also there's no guarentee that in future versions date will cast to float and back properly.
Hmmm, I need to test it out with DateTime2
Atif: The conversion to varchar is slower. It isn't noticable on a single value. On a couple million rows, it is.
CREATE TABLE #DateTest (
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DateValue DATETIME
)
INSERT INTO #DateTest (DateValue)
SELECT TOP 1000000
DATEADD(mi,RAND(s1.number*5000 + s2.number)*5000,'2000/01/01' )
FROM master..spt_values s1 CROSS JOIN master..spt_values s2
WHERE s1.[name] IS NULL AND s2.NAME IS null
GO
SET STATISTICS TIME ON
GO
-- CPU time = 781 ms, elapsed time = 13696 ms
SELECT dateadd(dd, datediff(dd,0, DateValue),0)
FROM #DateTest
GO
-- CPU time = 3015 ms, elapsed time = 15001 ms
SELECT convert(datetime,convert(varchar(10),DateValue,101))
FROM #DateTest
GO
DROP TABLE #DateTest
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
July 9, 2008 at 7:05 am
Nicely done, Gail... It shows that the Convert method takes about 4 times longer than the DateAdd/DateDiff method... While someone may not be working on a million rows, anytime you can increase the performance of simple functionality by a factor of 4, you know it's gonna help on large batch runs and GUI code under heavy load... and it's a very simple thing to do, to boot! 😀
Heh... no wonder you're an MVP. 😉
--Jeff Moden
July 10, 2008 at 9:30 am
Heh... no wonder you're an MVP.
I think the saying goes, it takes one to know one....
July 10, 2008 at 9:37 am
Jeff Moden (7/9/2008)
Nicely done, Gail... It shows that the Convert method takes about 4 times longer than the DateAdd/DateDiff method...
If I hadn't done a test, you probably would have.
And just for completeness, seeing as I'll probably blog on this sometime... This was run with the same test data as above.
-- CPU time = 922 ms, elapsed time = 13332 ms.
SELECT CAST(FLOOR(CAST(DateValue AS FLOAT)) AS DATETIME)
FROM #DateTest
GO
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
July 10, 2008 at 7:58 pm
AndyD (7/10/2008)
Heh... no wonder you're an MVP.
I think the saying goes, it takes one to know one....
Thanks Andy...
--Jeff Moden
July 10, 2008 at 8:00 pm
GilaMonster (7/10/2008)
Jeff Moden (7/9/2008)
Nicely done, Gail... It shows that the Convert method takes about 4 times longer than the DateAdd/DateDiff method...If I hadn't done a test, you probably would have.
And just for completeness, seeing as I'll probably blog on this sometime... This was run with the same test data as above.
-- CPU time = 922 ms, elapsed time = 13332 ms.
SELECT CAST(FLOOR(CAST(DateValue AS FLOAT)) AS DATETIME)
FROM #DateTest
GO
Nothing like being complete... thanks Gail. The problem is, now that I look at that, that's not quite the forumula I remember... I'll see if I can find the one I'm thinking about...
--Jeff Moden
July 10, 2008 at 8:28 pm
Aw crud... I've lost my mind... the method I was talking about doesn't have a thing to do with Float conversions... here's an (very) old set of tests I did. Note the code for "Rounding method 2"... THAT's what I was talking about...
--===== 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.#JbmTest
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.#JbmTest
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 1 ======'
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET @StartTime = GETDATE()
SELECT @MyDate=CAST(ROUND(CAST(ADate AS FLOAT),0,1) AS DATETIME) FROM dbo.#JbmTest
PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'
PRINT ' '
PRINT '===== Rounding method 2 ======'
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET @StartTime = GETDATE()
SELECT @MyDate=CAST(CAST((ADate - 0.5 ) AS INTEGER) AS DATETIME) FROM dbo.#JbmTest
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.#JbmTest
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.#JbmTest
PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'
PRINT ' '
PRINT '===== Johnathons''s Integer function (has flaw!) ====='
-- 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.#JbmTest
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.#JbmTest
PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'
PRINT ' '
PRINT '===== Minus method (int) ====='
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET @StartTime = GETDATE()
SELECT @MyDate=CAST(Adate - 0.50000004 as INT) FROM dbo.#JbmTest
PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'
PRINT ' '
--DROP TABLE #JbmTest
... sorry for the confusion...
--Jeff Moden
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply