August 15, 2011 at 5:07 am
Hi,
I have a requirement to display the exact date 3 months from today and also to only extract the date part by modifying the following code to get the desired result as- 2011-11-15:
DATEADD(MM, 3, GETDATE())
Currently the result I get is:
2011-11-15 12:01:57.693
Could someone know what else do I need to change in order to achieve this ?
Thanks,
Paul
---------------------
Thanks everyone, I got the solution to this one:
CONVERT(VARCHAR(10), DATEADD(MM, 3, GETDATE()), 120)
August 15, 2011 at 4:27 pm
You might want to check again... what happens on May 31st?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 15, 2011 at 4:37 pm
Jeff Moden (8/15/2011)
You might want to check again... what happens on May 31st?
I'm confused here Jeff, nothing bad happens on May 31 + 3 months. August 31st is a legal date.
On August 31st, however, you go to November 30th.
The killer is on June 30th, you go to Sept 30th, which is NOT the end of the month... but that may be gold plating this.
OP: To strip the time off your times quickly and easily, you do this: DATEADD( dd, DATEDIFF( dd, 0, <insert your datefunction here>), 0) This figures out the # of days since 1/1/1900 and re-adds it to 1/1/1900 without the timestamp component. It's the fastest way, much faster then converting to varchar, stripping the timestamp, and reverting it back to datetime.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 15, 2011 at 4:59 pm
This is good and simple too.
SELECT CAST(FLOOR(CAST(GetDate() AS FLOAT)) AS DATETIME)
August 15, 2011 at 5:14 pm
Chuck Hottle (8/15/2011)
This is good and simple too.SELECT CAST(FLOOR(CAST(GetDate() AS FLOAT)) AS DATETIME)
I was originally going to call this a bad idea, but I decided a little sample million row coding was in order before I did, just to see how much worse it would perform for the converts.
... It's better, at least on my machine. MAXDOP 1 was required for both to remove some nastiness parallelism was including (and slowing it down horribly). See the test code and results below:
IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE #test
SET NOCOUNT ON
CREATE TABLE #test (DatetimeWithTime DATETIME NOT NULL, datenotime DATETIME NULL)
INSERT INTO #test (DatetimeWithTime)
SELECT top 1000000
GETDATE()
FROM
syscolumns AS c1, syscolumns AS c2, syscolumns AS c3, syscolumns AS c4
GO
-- Start time test here.
PRINT CHAR(10)
PRINT 'DateNoTimeDateFxns'
SET STATISTICS TIME ON
UPDATE #test
SET DateNoTime = DATEADD(dd, DATEDIFF( dd, 0, DatetimeWithTime), 0)
OPTION (MAXDOP 1)
SET STATISTICS TIME OFF
PRINT CHAR(10)
PRINT 'DateNoTimeFloored'
SET STATISTICS TIME ON
UPDATE #test
SETDateNoTime = CAST(FLOOR(CAST(DatetimeWithTime AS FLOAT)) AS DATETIME)
OPTION (MAXDOP 1)
SET STATISTICS TIME OFF
GO 10
Sample of the results from the 10 iterations (remember to ignore the first one):
DateNoTimeDateFxns
SQL Server Execution Times:
CPU time = 1203 ms, elapsed time = 1198 ms.
DateNoTimeFloored
SQL Server Execution Times:
CPU time = 1156 ms, elapsed time = 1162 ms.
Not much difference, but 38ms is 38ms. Nice function, Chuck.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 15, 2011 at 11:35 pm
Evil Kraig F (8/15/2011)
Jeff Moden (8/15/2011)
You might want to check again... what happens on May 31st?I'm confused here Jeff, nothing bad happens on May 31 + 3 months. August 31st is a legal date.
On August 31st, however, you go to November 30th.
The killer is on June 30th, you go to Sept 30th, which is NOT the end of the month... but that may be gold plating this.
OP: To strip the time off your times quickly and easily, you do this: DATEADD( dd, DATEDIFF( dd, 0, <insert your datefunction here>), 0) This figures out the # of days since 1/1/1900 and re-adds it to 1/1/1900 without the timestamp component. It's the fastest way, much faster then converting to varchar, stripping the timestamp, and reverting it back to datetime.
I numbered the months BEFORE coffee! 😛 Your August and June examples where what I was after. And "gold plating" is a good thing if it's what is required. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2011 at 12:00 am
Evil Kraig F (8/15/2011)
Chuck Hottle (8/15/2011)
This is good and simple too.SELECT CAST(FLOOR(CAST(GetDate() AS FLOAT)) AS DATETIME)
I was originally going to call this a bad idea, but I decided a little sample million row coding was in order before I did, just to see how much worse it would perform for the converts.
... It's better, at least on my machine. MAXDOP 1 was required for both to remove some nastiness parallelism was including (and slowing it down horribly). See the test code and results below:
IF OBJECT_ID('tempdb..#test') IS NOT NULLDROP TABLE #test
SET NOCOUNT ON
CREATE TABLE #test (DatetimeWithTime DATETIME NOT NULL, datenotime DATETIME NULL)
INSERT INTO #test (DatetimeWithTime)
SELECT top 1000000
GETDATE()
FROM
syscolumns AS c1, syscolumns AS c2, syscolumns AS c3, syscolumns AS c4
GO
-- Start time test here.
PRINT CHAR(10)
PRINT 'DateNoTimeDateFxns'
SET STATISTICS TIME ON
UPDATE #test
SET DateNoTime = DATEADD(dd, DATEDIFF( dd, 0, DatetimeWithTime), 0)
OPTION (MAXDOP 1)
SET STATISTICS TIME OFF
PRINT CHAR(10)
PRINT 'DateNoTimeFloored'
SET STATISTICS TIME ON
UPDATE #test
SETDateNoTime = CAST(FLOOR(CAST(DatetimeWithTime AS FLOAT)) AS DATETIME)
OPTION (MAXDOP 1)
SET STATISTICS TIME OFF
GO 10
Sample of the results from the 10 iterations (remember to ignore the first one):
DateNoTimeDateFxnsSQL Server Execution Times:
CPU time = 1203 ms, elapsed time = 1198 ms.
DateNoTimeFloored
SQL Server Execution Times:
CPU time = 1156 ms, elapsed time = 1162 ms.
Not much difference, but 38ms is 38ms. Nice function, Chuck.
Then you should have an appreciation for the following, as well... 😉
--==============================================================================
-- Create a million rows of test data
--==============================================================================
--===== Supress the auto-display of row counts
SET NOCOUNT ON
;
--===== Conditionally drop the test table to make reruns in SSMS easier
IF OBJECT_ID('tempdb..#Test','U') IS NOT NULL
DROP TABLE #Test
;
--===== Create and populate the test able on the fly
SELECT TOP 1000000
DatetimeWithTime = GETDATE(),
DateNoTime = CAST(0 AS DATETIME)
INTO #Test
FROM sys.all_columns ac1,
sys.all_columns ac2
;
--==============================================================================
-- Test the different methods along with a very old "dark horse"
--==============================================================================
PRINT '========== DATEADD/DATEDIFF =========='
SET STATISTICS TIME ON
;
UPDATE #Test
SET DateNoTime = DATEADD(dd, DATEDIFF( dd, 0, DatetimeWithTime), 0)
OPTION (MAXDOP 1)
;
SET STATISTICS TIME OFF
;
PRINT '========== CAST FLOAT/FLOOR =========='
SET STATISTICS TIME ON
;
UPDATE #Test
SET DateNoTime = CAST(FLOOR(CAST(DatetimeWithTime AS FLOAT)) AS DATETIME)
OPTION (MAXDOP 1)
;
SET STATISTICS TIME OFF
;
PRINT '========== CAST DATEDIFF =========='
SET STATISTICS TIME ON
;
UPDATE #Test
SET DateNoTime = CAST(DATEDIFF(dd,0,DatetimeWithTime) AS DATETIME)
OPTION (MAXDOP 1)
;
SET STATISTICS TIME OFF
;
Results on my laptop machine...
========== DATEADD/DATEDIFF ==========
SQL Server Execution Times:
CPU time = 2745 ms, elapsed time = 7390 ms.
========== CAST FLOAT/FLOOR ==========
SQL Server Execution Times:
CPU time = 1654 ms, elapsed time = 1664 ms.
========== CAST DATEDIFF ==========
SQL Server Execution Times:
CPU time = 1279 ms, elapsed time = 1284 ms.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2011 at 12:05 am
A second run without rebuilding the test table shows a slightly different story, as well...
========== DATEADD/DATEDIFF ==========
SQL Server Execution Times:
CPU time = 1326 ms, elapsed time = 1320 ms.
========== CAST FLOAT/FLOOR ==========
SQL Server Execution Times:
CPU time = 1669 ms, elapsed time = 1666 ms.
========== CAST DATEDIFF ==========
SQL Server Execution Times:
CPU time = 1279 ms, elapsed time = 1289 ms.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2011 at 11:01 am
Jeff Moden (8/16/2011)
A second run without rebuilding the test table shows a slightly different story, as well...
========== DATEADD/DATEDIFF ==========SQL Server Execution Times:
CPU time = 1326 ms, elapsed time = 1320 ms.
========== CAST FLOAT/FLOOR ==========
SQL Server Execution Times:
CPU time = 1669 ms, elapsed time = 1666 ms.
========== CAST DATEDIFF ==========
SQL Server Execution Times:
CPU time = 1279 ms, elapsed time = 1289 ms.
mmmm, that's interesting. Different results for the doublecast with floor, but that's not a spectacular surprise (test test test!). The Cast + Datediff though outperforming the straight mathmatics without casts is very interesting, however.
You're right, I do have a bit of appreciation for that. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 16, 2011 at 11:31 am
Evil Kraig F (8/15/2011)
Jeff Moden (8/15/2011)
You might want to check again... what happens on May 31st?I'm confused here Jeff, nothing bad happens on May 31 + 3 months. August 31st is a legal date.
On August 31st, however, you go to November 30th.
The killer is on June 30th, you go to Sept 30th, which is NOT the end of the month... but that may be gold plating this.
OP: To strip the time off your times quickly and easily, you do this: DATEADD( dd, DATEDIFF( dd, 0, <insert your datefunction here>), 0) This figures out the # of days since 1/1/1900 and re-adds it to 1/1/1900 without the timestamp component. It's the fastest way, much faster then converting to varchar, stripping the timestamp, and reverting it back to datetime.
I am fairly certain that Sept 30th is the end of month. 🙂
However, when you add 3 months to November 30, it will return Feb 28 (or 29 in leap year), so exactly what the OP wants then is a bit of a question.
August 16, 2011 at 12:12 pm
Michael Valentine Jones (8/16/2011)
I am fairly certain that Sept 30th is the end of month. 🙂However, when you add 3 months to November 30, it will return Feb 28 (or 29 in leap year), so exactly what the OP wants then is a bit of a question.
*facepalm* Apparently Jeff isn't the only one in need of coffee.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 16, 2011 at 9:57 pm
Evil Kraig F (8/16/2011)
Jeff Moden (8/16/2011)
A second run without rebuilding the test table shows a slightly different story, as well...
========== DATEADD/DATEDIFF ==========SQL Server Execution Times:
CPU time = 1326 ms, elapsed time = 1320 ms.
========== CAST FLOAT/FLOOR ==========
SQL Server Execution Times:
CPU time = 1669 ms, elapsed time = 1666 ms.
========== CAST DATEDIFF ==========
SQL Server Execution Times:
CPU time = 1279 ms, elapsed time = 1289 ms.
mmmm, that's interesting. Different results for the doublecast with floor, but that's not a spectacular surprise (test test test!). The Cast + Datediff though outperforming the straight mathmatics without casts is very interesting, however.
You're right, I do have a bit of appreciation for that. 🙂
That's only half of what I meant... if we make the DoubleCast with Floor go first...
-- Create a million rows of test data
--==============================================================================
--===== Supress the auto-display of row counts
SET NOCOUNT ON
;
--===== Conditionally drop the test table to make reruns in SSMS easier
IF OBJECT_ID('tempdb..#Test','U') IS NOT NULL
DROP TABLE #Test
;
--===== Create and populate the test able on the fly
SELECT TOP 1000000
DatetimeWithTime = GETDATE(),
DateNoTime = CAST(0 AS DATETIME)
INTO #Test
FROM sys.all_columns ac1,
sys.all_columns ac2
;
--==============================================================================
-- Test the different methods along with a very old "dark horse"
--==============================================================================
PRINT '========== CAST FLOAT/FLOOR =========='
SET STATISTICS TIME ON
;
UPDATE #Test
SET DateNoTime = CAST(FLOOR(CAST(DatetimeWithTime AS FLOAT)) AS DATETIME)
OPTION (MAXDOP 1)
;
SET STATISTICS TIME OFF
;
PRINT '========== DATEADD/DATEDIFF =========='
SET STATISTICS TIME ON
;
UPDATE #Test
SET DateNoTime = DATEADD(dd, DATEDIFF( dd, 0, DatetimeWithTime), 0)
OPTION (MAXDOP 1)
;
SET STATISTICS TIME OFF
;
PRINT '========== CAST DATEDIFF =========='
SET STATISTICS TIME ON
;
UPDATE #Test
SET DateNoTime = CAST(DATEDIFF(dd,0,DatetimeWithTime) AS DATETIME)
OPTION (MAXDOP 1)
;
SET STATISTICS TIME OFF
;
... the code returns the followng results...
========== CAST FLOAT/FLOOR ==========
SQL Server Execution Times:
CPU time = 3120 ms, elapsed time = 3318 ms.
========== DATEADD/DATEDIFF ==========
SQL Server Execution Times:
CPU time = 1310 ms, elapsed time = 1324 ms.
========== CAST DATEDIFF ==========
SQL Server Execution Times:
CPU time = 1264 ms, elapsed time = 1274 ms.
... it actually looks worse that DATEDIFF/DATEADD did. The tests are "order sensitive" and that's what I was trying to make everyone realize because I knew the doublecast/float method wasn't going to be faster than even DATEDIFF/DATEADD.
Even the use of CheckPoint and DBCC DropCleanBuffers doesn't help much for this test.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply