Date and time manipulation in SQL Server

Question

0.00/5 (No votes)

See more:

Hi everybody, I am working on some project and I am almost finishing it but in the reporting phase I stacked. It is like this. The software (the project that I am working on) generates a report. The reporting service that I am using is Microsoft Report Viewer and I want to select and display all the customers that purchased items last month, from a single table into the report. Remember the report is going to be generated every month.

Posted 12-Mar-10 23:53pm

redietlove

Updated 13-Mar-10 8:54am

Deeksha Shenoy

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Answer 1 · 2010-03-13T01:02:00

redietlove wrote:
I want to select and display all the customers that purchased item last month

You need to write a SQL query on that table that will always return the records of 'last month'. Thus, whenever report is run, you get today-30 days data.

Set this (last month) parameter in your query the way you want.
1. You want last 30 days or so (has to be a fixed number).

2. Last month only. If today is 13th March, yet result set needs to be of just 1-28 Feb.

Accordingly, fetch the result with your appropriate WHERE clause.
Example:

SQL

Select 
   Id, Item, PurDate, Price 
From 
   PurTable 
Where 
   PurDate Between GetDate() AND (GetDate()-30)

redietlove wrote:
remember the report is going to be generated every month.

This part can be handled by your report server. Schedule it to generate and send it to users.