SQL Server 2005: how to subtract 6 month

sql sql-server-2005 datetime date datediff
skaeff picture skaeff · Oct 14, 2010 · Viewed 77.5k times · Source

I have a date, suppose today date 

declare @d datetime
set @d = '20101014'

I need 

select @d - <six month>

where is the real number of days that contains last six month, beginning from @d.

Answer

Alex Bagnolini picture Alex Bagnolini · Oct 14, 2010

You can use DATEADD:

select DATEADD(month, -6, @d)

EDIT: if you need the number of days up to 6 months ago you can use DATEDIFF:

select DATEDIFF(day, @d, DATEADD(month, -6, @d))

Related questions

SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More
Extracting hours from a DateTime (SQL Server 2005)

I can extract the month and day by using Day(Date()), Month(Date()). I can't extract hours, with HOUR(Date()). I get the following error. 'HOUR' is not a recognized built-in function name. How can I extract hours?

Read More
Datetime in where clause

How can I select 12/20/2008 in where clause of sql? The server is SQL server 2005. select * from tblErrorLog where errorDate = '12/20/2008'

Read More