selecting appointments for the last 12 months

Hi

I have a table called appointments, each appointment has a date, for example 13/03/2012 and a title...

I want to write a select statement to select all appointments in the last 12 monthS only , so for example if i ran the query today i would get all appointments back to  13/03/2011....i dont want to hard code the dates as i would like to run this anyday of the year and only get the previous 12 months of appointments.

Can anyone give me the syntax please?
ac_davis2002Asked:
Who is Participating?
 
Simone BConnect With a Mentor Senior E-Commerce AnalystCommented:
Select * from TBLAppointments
where AppointmentDate > dateadd(m,-12,getdate())
or (month(appointmentdate) = month(getdate()) and year(appointmentdate = year(getdate()) - 1)
0
 
Simone BSenior E-Commerce AnalystCommented:
Select * from TBLAppointments
where AppointmentDate > dateadd(d,-365,getdate())

This will give you all appointments with the date in the last 365 days.

You could also use months:

Select * from TBLAppointments
where AppointmentDate > dateadd(m,-12,getdate())

to get all appts within the last 12 months.
0
 
Anthony PerkinsCommented:
or simply (no points please)
WHERE YourAppointmentDate >= DATEADD(year, -1, GETDATE())
0
 
ac_davis2002Author Commented:
That is almost what i need but can i get it to go back to the first of the month so for example if I run today it will give me all appointments from today 14/03/2012 back to 01/03/2011? is this possible?
0
 
Anthony PerkinsCommented:
If you are concerned about performance and AppointmentDate is appropriately indexed this may prove a better approach (notice that it also takes in account the time):

DECLARE @StartDate datetime,
	@EndDate datetime

SET @EndDate = DATEADD(DAY, 1, DATEDIFF(DAY, 0, GETDATE()))
SET @StartDate = DATEADD(year, -1, DATEDIFF(DAY, 0, GETDATE()))
SET @StartDate = DATEADD(DAY, 1 - DAY(@StartDate), @StartDate)

...

WHERE AppointmentDate >= @StartDate AND AppointmentDate < @EndDate

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.