Need date: Today - 2 years..

Question

Need date: Today - 2 years..

Express12

SSCoach

Points: 18514
More actions
September 27, 2011 at 5:51 am

#390482

This statement renders 2 years ago today:
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()) -730, 0)
How can I modify the above statement to use the YEAR instead of dd -- to obtain same result?
(eg... YEAR -2)
thanks for any help !
BT

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

calvo SSChampion Points: 12930 More actions · Answer 1

Express12 (9/27/2011)
This statement renders 2 years ago today:
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()) -730, 0)
How can I modify the above statement to use the YEAR instead of dd -- to obtain same result?
(eg... YEAR -2)
thanks for any help !

You've already got it.

DATEADD(YEAR,-2,CURRENT_TIMESTAMP)

check out BOL for more date modifications and conversions.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

Express12 (9/27/2011)
This statement renders 2 years ago today:
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()) -730, 0)
How can I modify the above statement to use the YEAR instead of dd -- to obtain same result?
(eg... YEAR -2)
thanks for any help !

That's not what your statement is doing. . . only the -730 (365/2) is to do with changing the date. The rest of that statement is setting the time to 00:00:00

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Express12 SSCoach Points: 18514 More actions · Answer 3

thank you.. now, how might I modify the above solution:

SELECT DATEADD(YEAR,-2,CURRENT_TIMESTAMP)

to render a TIME = all 0's

BT

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

Express12 (9/27/2011)
thank you.. now, how might I modify the above solution:
SELECT DATEADD(YEAR,-2,CURRENT_TIMESTAMP)
to render a TIME = all 0's

SELECT DATEADD(dd, DATEDIFF(dd,0,DATEADD(YEAR,-2,GETDATE())), 0)

--Equivalent

SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()) -730, 0)

But it's really no better than your previous code

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

calvo SSChampion Points: 12930 More actions · Answer 5

Have you checked that link? There's all the date conversions and date manipulations you'll ever need there.

DATEADD(YEAR,-2,cast(CURRENT_TIMESTAMP as DATE))

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

calvo (9/27/2011)
Have you checked that link? There's all the date conversions and date manipulations you'll ever need there.
DATEADD(YEAR,-2,cast(CURRENT_TIMESTAMP as DATE))

Just to prove that I do listen when I'm taught something (message to Jeff Moden and Drew Allen who corrected my misconception in March)

/**********************************************************************************************************************

Purpose:

Create a voluminous test table with highly randomized DATETIME data.

--Jeff Moden

**********************************************************************************************************************/

--===== Do this test in a nice safe place that everyone has.

USE TempDB

;

--===== Conditionally drop the test table to make reruns easier

IF OBJECT_ID('dbo.JBMTest','U') IS NOT NULL

DROP TABLE dbo.JBMTest

;

--===== Create and populate a 1,000,000 row test table.

-- "SomeID" has a range of 1 to 1,000,000 unique numbers

-- "SomeDateTimeTime" has a range of >=01/01/2000 and <01/01/2020 non-unique date/times

SELECT TOP 1000000

SomeID = IDENTITY(INT,1,1),

SomeDateTime = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME)

INTO dbo.JBMTest

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

--===== This will take the display out of the picture so we

-- can measure the true processing time in memory.

DECLARE @BitBucketDATETIME DATETIME

PRINT '========== BASELINE =========='

SET STATISTICS TIME ON

SELECT @BitBucketDATETIME = SomeDateTime