October 10, 2008 at 8:07 am
Hi All
Is there any predefine methods in SQL server to get datetime on midnight format (2008-10-10 00:00:00.000), if i use GetDate(), it gives me the current time, i am using a UDF to create the above format, wondering is there any way on SQL 2005 to do this???
Cheers
🙂
October 10, 2008 at 8:23 am
There is no built-in function in SQL Server 2000/2005. (in 2008, you can use the DATE data type).
You can strip off the TIME part from a datetime value by running the following code.
SELECT DATEADD(d, 0, DATEDIFF(d, 0, GETDATE()))
.
October 10, 2008 at 8:29 am
Thanks Jacob
thats what i was looking for
🙂
October 10, 2008 at 8:31 am
You are welcome 🙂
.
October 10, 2008 at 4:55 pm
You can also use this...
SELECT convert(datetime,convert(varchar,GetDate(),10))
Just replace the GetDate() function with your passed in value.
It would be interesting to see what performed better. 😉
Gary Johnson
Sr Database Engineer
October 10, 2008 at 9:27 pm
Gary Johnson (10/10/2008)
It would be interesting to see what performed better. 😉
Actually the DateAdd/DateDiff method seems to perform slightly better (like 6 milliseconds better over 25,000 rows) than convert to varchar or char then back to date. In a close second (like 1 millisecond worse than DateAdd/DateDiff over 25,000 rows) was convert to float then floor it and convert back to date. OK, so I was REALLY bored today. :hehe:
October 11, 2008 at 8:24 am
More than slightly better...
http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/
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
October 11, 2008 at 10:09 am
Chris Harshman (10/10/2008)
Gary Johnson (10/10/2008)
It would be interesting to see what performed better. 😉Actually the DateAdd/DateDiff method seems to perform slightly better (like 6 milliseconds better over 25,000 rows) than convert to varchar or char then back to date. In a close second (like 1 millisecond worse than DateAdd/DateDiff over 25,000 rows) was convert to float then floor it and convert back to date. OK, so I was REALLY bored today. :hehe:
It would be really cool if you posted your test code... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 11, 2008 at 11:11 am
Guess I'll throw the testing I've done in the past into the ring... 🙂
The testing below takes the "display" out of the picture using a throw-away variable of the DATETIME datatype as the destination for the results. You'll be interested in how fast an implicit conversion can really be as well as how the order of the DATEADD/DATEDIFF parameters affect things...
First, here's my usual general purpose million row test table... it doesn't take long to run... try it...
DROP TABLE JBMTest
GO
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
-- for all rows.
-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
-- Jeff Moden
SELECT TOP 1000000
RowNum = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
SomeHex12 = RIGHT(NEWID(),12)
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1
CROSS JOIN Master.dbo.SysColumns t2
--===== A table is not properly formed unless a Primary Key has been assigned
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (RowNum)
... and, here's the tests... these work on both 2k and 2k5. According to Gail's fine blog, some of these tests will not work on 2k8 (which totally pisses me off... they keep taking away "features" that break good folks code)...
DECLARE @BitBucketDATETIME DATETIME
PRINT '========== BASELINE =========='
SET STATISTICS TIME ON
SELECT @BitBucketDATETIME = SomeDate
FROM dbo.JBMTest WITH (NOLOCK)
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== DATEADD/DATEDIFF 1 =========='
SET STATISTICS TIME ON
SELECT @BitBucketDATETIME = DATEADD(d, 0, DATEDIFF(d, 0, SomeDate))
FROM dbo.JBMTest WITH (NOLOCK)
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== DATEADD/DATEDIFF 2 =========='
SET STATISTICS TIME ON
SELECT @BitBucketDATETIME = DATEADD(dd,DATEDIFF(dd,0,SomeDate),0)
FROM dbo.JBMTest WITH (NOLOCK)
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== DATEDIFF Implicit =========='
SET STATISTICS TIME ON
SELECT @BitBucketDATETIME = DATEDIFF(d, 0, SomeDate)
FROM dbo.JBMTest WITH (NOLOCK)
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== CONVERT =========='
SET STATISTICS TIME ON
SELECT @BitBucketDATETIME = CONVERT(DATETIME,CONVERT(VARCHAR,SomeDate,100))
FROM dbo.JBMTest WITH (NOLOCK)
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== CONVERT Implicit=========='
SET STATISTICS TIME ON
SELECT @BitBucketDATETIME = CONVERT(VARCHAR,SomeDate,100)
FROM dbo.JBMTest WITH (NOLOCK)
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '===== FLOOR ====='
SET STATISTICS TIME ON
SELECT @BitBucketDATETIME = CAST(FLOOR(CONVERT(FLOAT, SomeDate)) AS DATETIME)
FROM dbo.JBMTest WITH (NOLOCK)
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '===== FLOOR Implicit ====='
SET STATISTICS TIME ON
SELECT @BitBucketDATETIME = FLOOR(CONVERT(FLOAT, SomeDate))
FROM dbo.JBMTest WITH (NOLOCK)
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '===== ROUNDING 1 ====='
SET STATISTICS TIME ON
SELECT @BitBucketDATETIME = CAST(CAST(SomeDate - 0.50000004 AS INT) AS DATETIME)
FROM dbo.JBMTest WITH (NOLOCK)
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '===== ROUNDING 1 Implicit ====='
SET STATISTICS TIME ON
SELECT @BitBucketDATETIME = CAST(SomeDate - 0.50000004 AS INT)
FROM dbo.JBMTest WITH (NOLOCK)
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '===== ROUNDING 2 ======'
SET STATISTICS TIME ON
SELECT @BitBucketDATETIME = CAST(ROUND(CAST(SomeDate AS FLOAT),0,1) AS DATETIME)
FROM dbo.JBMTest WITH (NOLOCK)
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '===== ROUNDING 2 Implicit ======'
SET STATISTICS TIME ON
SELECT @BitBucketDATETIME = ROUND(CAST(SomeDate AS FLOAT),0,1)
FROM dbo.JBMTest WITH (NOLOCK)
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
Here are the results I got on my humble 6 year old P5 1.8Ghz with 1 GB Ram and IDE hard drives on 2k5...
[font="Courier New"]========== BASELINE ==========
SQL Server Execution Times:
CPU time = 781 ms, elapsed time = 882 ms.
================================================================================
========== DATEADD/DATEDIFF 1 ==========
SQL Server Execution Times:
CPU time = 1265 ms, elapsed time = 1318 ms.
================================================================================
========== DATEADD/DATEDIFF 2 ==========
SQL Server Execution Times:
CPU time = 969 ms, elapsed time = 1001 ms.
================================================================================
========== DATEDIFF Implicit ==========
SQL Server Execution Times:
CPU time = 860 ms, elapsed time = 888 ms.
================================================================================
========== CONVERT ==========
SQL Server Execution Times:
CPU time = 6140 ms, elapsed time = 6414 ms.
================================================================================
========== CONVERT Implicit==========
SQL Server Execution Times:
CPU time = 6453 ms, elapsed time = 6689 ms.
================================================================================
===== FLOOR =====
SQL Server Execution Times:
CPU time = 1282 ms, elapsed time = 1292 ms.
================================================================================
===== FLOOR Implicit =====
SQL Server Execution Times:
CPU time = 1218 ms, elapsed time = 1243 ms.
================================================================================
===== ROUNDING 1 =====
SQL Server Execution Times:
CPU time = 891 ms, elapsed time = 904 ms.
================================================================================
===== ROUNDING 1 Implicit =====
SQL Server Execution Times:
CPU time = 922 ms, elapsed time = 952 ms.
================================================================================
===== ROUNDING 2 ======
SQL Server Execution Times:
CPU time = 1703 ms, elapsed time = 1767 ms.
================================================================================
===== ROUNDING 2 Implicit ======
SQL Server Execution Times:
CPU time = 1516 ms, elapsed time = 1581 ms.
================================================================================
[/font]
... and here's the same test on 2k...
[font="Courier New"]========== BASELINE ==========
SQL Server Execution Times:
CPU time = 5 ms, elapsed time = 5 ms.
SQL Server Execution Times:
CPU time = 594 ms, elapsed time = 1088 ms.
================================================================================
========== DATEADD/DATEDIFF 1 ==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 969 ms, elapsed time = 994 ms.
================================================================================
========== DATEADD/DATEDIFF 2 ==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 875 ms, elapsed time = 894 ms.
================================================================================
========== DATEDIFF Implicit ==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 734 ms, elapsed time = 748 ms.
================================================================================
========== CONVERT ==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 10156 ms, elapsed time = 10230 ms.
================================================================================
========== CONVERT Implicit==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 10188 ms, elapsed time = 10298 ms.
================================================================================
===== FLOOR =====
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1906 ms, elapsed time = 1968 ms.
================================================================================
===== FLOOR Implicit =====
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1885 ms, elapsed time = 1885 ms.
================================================================================
===== ROUNDING 1 =====
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 730 ms, elapsed time = 730 ms.
================================================================================
===== ROUNDING 1 Implicit =====
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 703 ms, elapsed time = 713 ms.
================================================================================
===== ROUNDING 2 ======
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3063 ms, elapsed time = 3066 ms.
================================================================================
===== ROUNDING 2 Implicit ======
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3031 ms, elapsed time = 3082 ms.
================================================================================
[/font]
I'm thinking that folks should avoid the CONVERT method for truncating dates. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 11, 2008 at 12:25 pm
Jeff Moden (10/11/2008)
... and, here's the tests... these work on both 2k and 2k5. According to Gail's fine blog, some of these tests will not work on 2k8 (which totally pisses me off... they keep taking away "features" that break good folks code)...
Where did I say that?
All your tests work fine on 2008. One of the ones I did (on the page I linked above) won't work on 2005 or lower because I use the DATE datatype. Some won't work when using the new datetime2 datatype as Datetime2 cannot be converted (explicitly or implicitly) to int or float.
They work fine on the old datetime though
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
October 11, 2008 at 2:42 pm
GilaMonster (10/11/2008)
Jeff Moden (10/11/2008)
... and, here's the tests... these work on both 2k and 2k5. According to Gail's fine blog, some of these tests will not work on 2k8 (which totally pisses me off... they keep taking away "features" that break good folks code)...
Where did I say that?
All your tests work fine on 2008. One of the ones I did (on the page I linked above) won't work on 2005 or lower because I use the DATE datatype. Some won't work when using the new datetime2 datatype as Datetime2 cannot be converted (explicitly or implicitly) to int or float.
They work fine on the old datetime though
Oh, sorry... my bad. It was Peso that said he didn't think that float (one that I've included above) would work in 2k8.
And, heh... guess I'm still angry at the fact that the DateTime2, Date, and Time won't convert.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2008 at 2:44 am
And, heh... guess I'm still angry at the fact that the DateTime2, Date, and Time won't convert.
Why? Other than conversions I haven't found a good reason to convert a date to a float or int.
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
October 12, 2008 at 9:15 am
GilaMonster (10/12/2008)
And, heh... guess I'm still angry at the fact that the DateTime2, Date, and Time won't convert.
Why? Other than conversions I haven't found a good reason to convert a date to a float or int.
Heh... and you won't because it's not possible. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2008 at 9:21 am
Other than conversions I haven't found a good reason to convert a datetime to a float or int.
I'm sure you could figure out what I meant.
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply