August 24, 2011 at 4:08 am
Good morning guys,
Sorry if this post sounds like its from a beginner but I'm stuffed trying to get his to work.
I am trying to get an SSRS report that will allow me to enter a date then it will subtract 7 days from the date entered and report everything for the previous 7 days.
I have a database where the dates are stored as smalldatetime, I want to enter the dates in the format 23/08/2011 and then this report on the last 7 days.
The issue I have is I cannot get the report or even the SQL code to give me the correct data, I've tried all different conversions and cannot quite get it right, my current code is below and is quite possibly incorrect
declare
@date varchar
set
@date = '23/08/2011'
SELECT distinct(P.Id), ref.Reference_Text_IN,
ref.Reference_Text_OUT, ref.Short_Surname_OUT,
ref.Number_OUT, ext.Details,
convert(datetime,ext.Status_Datetime,103) as 'Date'
FROM dbo.Exception_Table ext
INNER JOIN Pay P
ON ext.Payment_Id = P.Id
inner join sup_reference_text ref
on p.number = ref.number_out
WHERE cast(ext.Status_Datetime as varchar) >= @date
This is bringing back all results, the dates are still showing the wrong format for example 2010-11-10 21:15:00.000 and the distinct is still showing results for all payment IDs.
What I need to know is how I convert all the dates into the same format so that when I enter the date as 23/08/2011 it comes back with the correct results and shows them in the report as 23/08/2011. I'm getting very confused.
August 24, 2011 at 4:38 am
In SSRS, format the datetime column like this : -
=Format(Parameters!DATETIMECOLUMN.Value,"dd/MM/yyyy")
August 24, 2011 at 5:52 am
declare @date DATETIME
set @date = CONVERT(DATETIME,CONVERT(varchar,GETDATE() - 7 ,103),103)
SELECT distinct(P.Id), ref.Reference_Text_IN,
ref.Reference_Text_OUT, ref.Short_Surname_OUT,
ref.Number_OUT, ext.Details,
convert(datetime,ext.Status_Datetime,103) as 'Date'
FROM dbo.Exception_Table ext
INNER JOIN Pay P
ON ext.Payment_Id = P.Id
inner join sup_reference_text ref
on p.number = ref.number_out
WHERE ext.Status_Datetime between @date and getdate()
this will automatically populate last 7 days.
if still this is not giving desired result kindly post the table structures with primary key details.
Regards
Durai Nagarajan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply