x

Calculate last 6 months

Hi all,

I am looking at running a report which will look at records added over the last 6 months. The date field is named [DateCreated] and I will be running the report on the first day of every month.

Does anyone know of any SQL that will automatically calculate the last 6 months?

Thanks

more ▼

asked Mar 22 '10 at 02:29 PM in Default

Chris Rolfe gravatar image

Chris Rolfe
21 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

for a report parameter for the end of the report you could use this as the parameter default value to get the last day of prev month

=cdate(Format(dateadd("d",(day(now()))*-1,now()),"dd/MMM/yyyy"))

To get the start date, 6 months prior to today, use the function supplied by Kev

more ▼

answered Mar 22 '10 at 02:42 PM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

+1 for referencing Kev and providing a good solution.
Mar 22 '10 at 03:15 PM CirqueDeSQLeil
+1: I hadn't even twigged it was for SSRS!
Mar 22 '10 at 03:24 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
...
where datecreated > dateadd(mm, -6, getdate())
...

or do you need a better definition of 'month'?

more ▼

answered Mar 22 '10 at 02:33 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 44 49 76

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x43
x24

asked: Mar 22 '10 at 02:29 PM

Seen: 3374 times

Last Updated: Mar 22 '10 at 02:29 PM