Date Parameter Problems

Aug 7, 2007

Hello Im having a date problem

I have a parameter called @Param_Daterange with the following values

Last 30 days =Today.AddDays(-30)
Last 60 days =Today.AddDays(-60)

My SQL has a chart which uses the following SQL


SELECT Sum(dbo.tbl_OMDQ_Aggregates.Value) AS SumOfValue, dbo.tbl_OMDQ_Aggregates.status_desc, dbo.tbl_OMDQ_Aggregates.DateRun
FROM dbo.tbl_OMDQ_Aggregates
WHERE (((dbo.tbl_OMDQ_Aggregates.SaleMonth) IN (@Param_SaleMonth))and (status_desc IN (@Param_ChurnType))) and dbo.tbl_OMDQ_Aggregates.DateRun > @Param_DateRange
GROUP BY dbo.tbl_OMDQ_Aggregates.status_desc, dbo.tbl_OMDQ_Aggregates.DateRun;

In visual studio the chart is working fine, however in IE the data getting returned is only a small subset off the entire data, ...my IE language setting is at English Aus, I also created a textbox so i can see what value the daterange parameter is passing and it seems to be in the correct format...the graph is also in the right day/time format of dd/MM/yyyy. However i have no idea which date is getting passed incorrectly, somewhere the date is getting switched to American format however what is outputting on the screen is in the right format...just not the correct records are returning

can anyone please help

View 1 Replies


ADVERTISEMENT

How To Use Default Parameter Values With A Date Parameter From A Cube/Reducing Parameters

Oct 15, 2007



Hi,

I have parameters in my report. The user can choose the year, month and date (3 parameters).
Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.

My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension .
Does somebody know how to make it possible to set default values for this parameters?



Other question :

Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007".
But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All".
Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.


Thanks !

JF

View 7 Replies View Related

SSRS Date Parameter - Showing Date Format On Right Hand Side

Jul 24, 2007

I am having report parameter end date I am showing the default value "5/21/2007 11:59:59 PM" in the end date paramter. And also I want to show date time format "(MM/DD/YYYY HH:MMS)" in the right hand side of the parameter. How to do this?



Thank You

View 1 Replies View Related

Date Parameter In SSRS - Allow Drop Down For A Date Range To Be Selected

Aug 11, 2013

Date parameter. I created a report that allows a drop down for a date range to be selected. However, whenever I preview the report, I get an error. I know my error stems from my date fields being in this format "201301" , and the "date/ time" in SSRS being mm/dd/yyyy on the drop down calendar in SSRS.

I know the direction I want to go in, but just a little confused on where would I use the convert or cast function. Would it be in the data parameter itself, or a part of the query before the @start date and @End date?

View 18 Replies View Related

Unable To Set Current Date In Date Picker Parameter

Nov 21, 2007

Hi,
I tried to create parameters in my report. I need one date picker. So i select data type as datetime. in default values i selected as non-queried and in date time functions i selected =Today. But am unable to set current date. Please help me to solve it.

Regards
Nataraj.C



View 2 Replies View Related

Date Time Format For Date Parameter

Jan 24, 2008



Hello,

I am using the calender parameter and I need to convert my data date format to the one that matched that is returned on selecting a date from this calender. Can you show me what this format is.

how can I convert my existing date format to this format. The existing date format is 2007-07-26 21:27:13.000

thank you
Kiran

View 5 Replies View Related

Transact SQL :: Expand Date Within Date Parameter

Jul 23, 2015

I am working into a t-sql where i need to expand the date within a date parameter. For example:

Start Date = '1/1/2014'
End Date = '12/31/2014'

I want to expand this as:

01/2014
02/2014
03/2014
04/2014
05/2014
06/2014
up to 
12/2014

View 4 Replies View Related

Date Parameter

Jan 16, 2008

store procedure
 @End_Date datetime
SELECT * FROM table1 WHERE PROCESS_DATE <= @End_Date
@End_Date format: 1/1/2008
PROCESS_DATE format: 1/1/2008 12:45:01 PM
I can not get the date after running the procedure using the value/format above.  I think the problem is that SQL convert @End_Date value to 1/1/2008  00:00:00:AM, which makes 1/1/2008 12:45:01 PM > 1/1/2008
Since I only need to compare the date (no need time), how can I resolve the problem from the C# code or SQL level?  Is that possbile to make 1/1/2008 default to the max datetime like 1/1/2008  11:59:59:PM?

View 4 Replies View Related

Date Parameter

Apr 2, 2008

Hi,

My boss would like to see on our report a code that would pull the date 01-01-2008 to the present. They want to be able to run this report whenever they wanted to. What is the best way to create this code.

Could I just use my field, which is "clm_dout" to say Clm_dout >= or is there another type of code I should use?

Thanks

View 8 Replies View Related

Date Parameter

Feb 21, 2006

Hello ........

I have a DateTime parameter in my report.

I want the last date of the current month selected as default in the date parameter(datetime picker ).

Can anybody help me ?

Thanks

View 7 Replies View Related

Date Parameter

Mar 22, 2007

hi and thanx in advance
in a report, i am getting data from a stored proc which take to date parameter fromdate and todate which are passed from report.
user select dates in datepicker control
but problem is that it not returning any record
Can problem be in date format difference in report and sql server ?

View 1 Replies View Related

Date Parameter

May 21, 2007

One of the fields in the table is of type smalldatetime.
When I select a date from the report parameter (a calender), the report produces an error: The value provided for the report parameter 'ValueDate' is not valid for its type.
Please note that if I use a report parameter of type string and enter 21 may 2007 the report works. But if a date is selected from the calender control i.e. 21/05/2007 the report produces the above error.

How is it possible to use the calender parameter without the report giving the error pls?
Thanks

View 4 Replies View Related

Search Parameter By Date

Oct 20, 2006

Hello guys I hope everyone is doing well I have a question. I have a database thats an ADP. on one of the forms I have a command button that has a stored procedure in it that will allow them to search by TM# or name or whatever, (I have the code below) I would like them to be able to do the samething but by date rather then TM# or Name, how would I go about doing that with a datetime datatype??

thank you

ALTER PROCEDURE dbo.Search_ActiveConditionals
(@Enter_TM# int)
AS SELECT TM#, LASTNAME, FIRSTNAME, CONDITIONAL, DATEOFCONDITIONAL, INVESTIGATOR_COND, REASONFORCOND
FROM dbo.ACTIVE_CONDITIONALS
WHERE (TM# = @Enter_TM#)

View 2 Replies View Related

Date Range Parameter

Oct 5, 2007

Hi,
I have a situation where i need to have two parameters (startDate, endDate) and use them as filters for my query. But i don't want to have a stored procedure with these parameters. So how can i have the two date parameters as calendars to choose dates from?

View 2 Replies View Related

Help With Date Parameter And If/else Statement

Jul 20, 2005

Hi,I'm trying to run a select statement that takes includes an if/elseclause. I need to select the 'tran_date' between....if the current month is greater than 10 i.e. after OCT then thetran_date will be between '01-Oct' - plus current year or thetran_date is '01-Oct' plus previous year.and the current dateHere is my script so far:SELECT a.resource_code ASSOCIATE, a.tran_date START_DATE,b.description PROJECT_CODE, sysdateFROM actrans a, acactivity b, dual cWHERE a.resource_type = 'E'AND a.acct_category = 'TIME'and a.activity like '9-WW-357852%'and b.activity = a.activityand tran_date betweenif to_char(sysdate, 'MM') > 10)begina.tran_date = '01-Oct' & to_char(sysdate, 'YY')endelsebegina.tran_date = '01-Oct' & to_char(sysdate, 'YY'- 1))EndORDER BY tran_dateCan anyone help me out with this? I need to run it as a job, but notsure whether I should be using a stored procedure etc..Thanks in advance.George

View 1 Replies View Related

Parameter Date Validation...

Feb 13, 2008

Hi all,

I have a report that have 2 date parameters (start and end date). How would I check that end date parameter move be equal or greater than Start date?

thanks

View 4 Replies View Related

Parameter Date Format!

Feb 1, 2007

Hi,

How to control the Datetime Parameter's format, such as I need the user input Date and Time, but default, the user can only select date, but can not input the time.

How to solve it?

Thank you.

View 1 Replies View Related

Date Picker Parameter With MDX...

May 8, 2007

I am sure others have ran into this issue.

But I need to have date parameters using the datetime type and I need to be able to pass the correct value to the MDX.

View 1 Replies View Related

Date Parameter Issue

Jan 3, 2007

hi all

We are a multi site / multi language company so do not want to hardcode the date format - these reports will be on intranet

Using MS Sql - Reporting Services 2005

Using no formatting on fields = yyyy-mm-dd - is how it is displayed.

I added a @startdate and an @enddate parameter

I read up and saw this information:

Report properties set

'Language' setting to '=User!Language' to detect user
culture automatically

My regional settings are set to English (New Zealand)

but whatever I do I receive no data - (data is definately there eg when run without parameters) so it is obviously the dates incorrect.

Can someone give me some advice on what else to check.

View 19 Replies View Related

Date Query Parameter

Feb 19, 2007

Hi There,

I've only just starting working with RSS2005. I've started designing basic reports that pulls info from our HEAT DB (incident/change logging system).

One of the report requirements is to be able to pull reports for calls that were logged for specific day/date, using the calllog.recvddate field in the HEAT DB.

What i would like to know is how do i do this ? i.e. how do i setup the date query parameter so that the user can select a date from the calender picker that returns all calls logged for the selected date ? How do i link the date parameter to the calllog.recdvddate field ?

Thanks,

RSS2005 NOOB

View 3 Replies View Related

Defaulting Date Parameter

Jul 13, 2007

Hello,



I have a report parameter StartDate. Properties are

DataTypeataTime

Prompt: StartDate

Default Values:

Non Queried : =NOW()



I set the default value to Now(). When I go to preview, the StartDate parameter is blank and its been locked & grayed out. I also tried



Today() and Globals!SystemTime but that does not work either. Is there any other solution to make this work?



Thanks

Raj





View 4 Replies View Related

Problem With A Date Parameter In ASP.Net Page

May 9, 2007

The date format in the 'weekending' textbox is 5/5/2007Here's what I've got:
With cmd.Parameters.Add(New SqlParameter("@SQLweekending", SqlDbType.DateTime, weekending.Text))the error message is:"Conversion from string "4/8/2007" to type 'Integer' is not valid."
Then, I tried:.Add(New SqlParameter("@SQLweekending", SqlDbType.DateTime, CDate(weekending.Text)))but, before I even tried it, it gave me a blue squiggly line, with an error message:"Error 2 Value of type 'Date' cannot be converted to 'Integer'"
How can I get this to work?

View 3 Replies View Related

How To Enter A Date Parameter In Debugger

Jun 22, 2004

(This is prob. a really dumb question but it's driving me mad!!...)

I am using the Debugger in SQL Query Analyzer & want to set the value of a datetime parameter prior to executing the stored proc. The "Debug procedure" window allows me to specify the parameter values - but I can't get it to accept a datetime. The language is us_english & I've tried most ways if specifying the date - 01/02/2004, with/out quotes, 02 Jan 2004, as a full datetime, swapping day/month values etc etc. The procedure always fails immediately with: Invalid character value for cast specification.

Thanks.

View 1 Replies View Related

How To Insert A Date Range Parameter

Sep 16, 2013

I am trying to pull MTD and YTD sales for this year and last year.

I have this line to pull all invoices between two dates:

WHERE (invc_hdr.ivh_dt Between '08/01/2013' And '08/31/13')

But I want to be able to put the dates in without having to change the code each time. I want a window to pop up so I can input the date range. Is there a way to do this.

View 5 Replies View Related

Using A Year Parameter In A Date Condition

Mar 11, 2008

I am collecting a year parameter from my user and want to use that year as part of a complete date condition.

So I collect the year say '2003' from my user.

A condition must be applied so that the date must be after 01-APR-...and here is where i want to utilize the parameter @Year. So I can apply the same date condition depending on what year we are looking at.

I am currently using the following logic:

(DATEPART(yy, CANCELLATION_DATE) = @Year)
AND
(DATEPART(mm, CANCELLATION_DATE) >= 04)
AND
(DATEPART(dd, CANCELLATION_DATE) >= 02)

This causes problems as the date can be only Jan, Feb, Mar, Apr 01 or 02. What I need is to gather all dates prior to Apr 02, with the year paramter to follow.

We originally developed this querty using Oracle so idealy there would exist a pl-sql to t-sql conversion tool out there! Anybody know of one?

Here is how the pl-sql is structured:
CANCELLATION_DATE >= TO_DATE('02-APR-'||:"Year",'DD-MON-YYYY') ) ) )

Thanks in advance,
mpaskevi

View 3 Replies View Related

How Do I Use A Date Parameter Correctly In A SP To Be Used With SSRS

Mar 24, 2008



I am having trouble adding a smalldatetime parameter @ScrubbedDate to my procedure and getting it to work with my report.

I get the cannot convert string to smalldatetime error.

View 6 Replies View Related

Restricting Date Parameter Values

Feb 22, 2007

Would like to restrict the dates users can put as parameters. For example, I have a report showing all orders in year 2007. I have exposed Start Date and End Date parameters. When users click the 'View Report' button, the report will filter for orders where attribute EnteredDate is >= Start Date and <= End Date.

I want to restrict the users from entering a start date greater than today's date. Would also like to restrict them from entering an end date that is less than the start date parameter. How can I do this?

TIA.
ElSalsero

View 5 Replies View Related

Assigning Current Date To A DTS Parameter

Oct 28, 2007



How do I assign the current date to a DTS parameter using a script - the following fails:


Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.VisualBasic

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dts.Variables("RunDate").Value = Now.Date

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Error message:

Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.

Thanks.

Dan

View 3 Replies View Related

Last Monday Date As Default Parameter Value

Dec 3, 2007



I have a stored procedure that grabs the Region key from the user, and the date. But i want to set up 5 reports, for day of the week. So i want to make the second parameter a defualt parameter set by me in each report. which is better then having to create 5 stored procedures with the same info just different dates.
when i try to enter the same data i have in my stored procedure to get Mondays date, i get an error.

Here is what i entered in the report parameter default expression box for the date :


=DATEADD(dd, - 6, DATEADD(dd, 1 - DATEPART(w, GETDATE()), CONVERT(varchar, GETDATE(),

101))))


but i keep getting an error that says:


The Value expression for the report parameter €˜Daily_activity_statistics_datetime€™ contains an error: [BC30451] Name 'dd' is not declared.

Build complete -- 1 errors, 0 warnings



whats wrong with it? This works fine in the stored procedure for the where clause. Please help! this would save me alot of time.

View 3 Replies View Related

How To Filter The Report With The Date Parameter.....

May 5, 2008



Hi All,

I have to filter my report with the date parameter. Custom Dates are comming from the database.

I have to filter with the date just previous Date which user selected as a parameter.

As an example if parameter contains 2/04/2008 , 10/04/2008, 14/04/2008, 16/04/2008 and user is selecting 14/04/2008 then I want to filter the data from just previous parameter i.e. 10/04/2008

Ritesh Kumar

View 5 Replies View Related

Using 1 Parameter To Select A Date Range

Apr 23, 2008

I have a series of "Payroll Periods", called S1 thru S26 for the year. I want to create a parameter call "Pay Period" and have the user select a single pay period, i.e. S13. Elsewhere I know the beginning and ending date for each pay period, but it is in another dataset. So if the user selects S13 and I know the 13th pay period is April 1st thru April 15th, how do I use those dates as my beginning and end dates for my SQL query statement to return only the data from the selected pay period. The data itself has dates and not pay period references.

View 1 Replies View Related

Date Format - Parameter/Field

Jan 24, 2007

Hi,

I have some difficulties to format a Date value in my reporting service.

As you know you can use this kind of expression:

=DateTime.Parse(Parameters!ReportingDate.Value).ToString("dd/MM/yyyy")

But it does not work, and when I just want to use the format property of the textbox content such as "dd/MM/yyyy", it displays "dd/MM/yyyy", not the real formatted data, this property works for "=Today()" but not for parameters and fields. What is the diffrence?

Have you an idea to format a Parameter/Field of Date type such as dd/MM/yyyy?

Many thanks!

View 8 Replies View Related

Date Parameter And Text Box Question

Jan 4, 2007

I have a report that runs transcripts for people. I have a date parameter setup along with last and first name. The date parameter field is used to specify what year (ex. 1/1/2006 - 12/31/2006) to pull the information for.

Now..I have a text box at the bottom (Footer) of my report that says something to the affect of "Official transcripts for 2006". There will be times when I have to run the report using a different year other then "2006" in the date parameter field. How do I have the text at the bottom change to reflect the year I'm reporting on? If I use dates say in 2004 I need the text at the bottom to reflect that year and not 2006. Can this be accomplished?

Thx,
Bill

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved