CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005
ThisIsLegend1016
ThisIsLegend1016

Reputation: 105

Get todays date a year ago

I have a script where I need today date but from a year ago, however I also need it to be in the following format with the time.

 2017-11-07 00:00:00.000

I currently have got to a place where I have last years date just not the 00:00:00.000 timestamp.

select  DATEADD(year, -1, GETDATE())

This returns the time of the time the query was ran.

 2017-11-07 13:37:10.770

This is for a where clause as I need to get some data from today's date from last year so looking for a where clause that starts at this date last year at midnight and finishes at 23:00:00.000

It's for a client running SQL Server 2005.

Upvotes: 1

Views: 1662

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522244

Try using:

CONVERT(DateTime, DATEDIFF(DAY, 0, DATEADD(year, -1, GETDATE())))

Here is a query to see this in action:

SELECT
    GETDATE() AS today,
    DATEADD(year, -1, GETDATE()) AS today_last_year,  -- what you already have
    CONVERT(DateTime, DATEDIFF(DAY, 0, DATEADD(year, -1, GETDATE()))) AS
        today_last_year_midnight;

This returned (as of the time of writing this answer):

enter image description here

Upvotes: 6

Related Questions