I have a sqlDataSource control that has its select command and parameters set dynamically. One of the parameters is a date, which should be in smalldatetime format (ie, 12/22/2006). Obviously, using SQL Server, the data is stored with both date and time. The problem is, I cannot get any data selected. I initially thought that I needed to tell it to only look at the date and not the time, but from what I have seen this should work. Here is the pertinent code.
dim seldate as datetime
selDate = Session("ChosenDate")
Dim SelCmd As String = "SELECT [PatientName], [AssignedTo], [CPT], [CPT2], [HospitalID], [RoomNbr], [ACType], [TxDx1], [TxDx2], [MRNbr], [Notes], [PatientID], [PCPID], [ResidentID], [Status], [CaseID], [AdmitDate], [crtd_datetime] FROM [DailyBilling] WHERE ([crtd_datetime] = @crtd_datetime) and ([AssignedTo] = @AssignedTo) and ([HospitalID] = @HospitalID) ORDER BY [PatientName]"
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
I have a question I need my users to be able to enter a between statement into an Edit Parameters form I have created for them. The reason being is so that they can find out when employees are due for a renewal (which is every two years from their final suit date..for example if their final was done on 12/01/2006 then they would be due to renew 12/31/2008). The code below allows them to look up final suit dates of individuals. I would like them to look up Renewals based on final suits. like: BETWEEN 12/01/2006 AND 12/31/2006. and get the results, does that make sense???
SELECT TM#, LASTNAME, FIRSTNAME, CONDITIONAL, DATEOFCONDITIONAL, FINALSUITDONE FROM dbo.EmployeeGamingLicense WHERE (FINALSUITDONE = @Enter_FINALSUITDONE)
I've gotten sort of fed up with dealing with regional date settings onthe client side and am considering the following scheme - justwondering if anyone has a negative view of it or not:Instead of@StartDate datetime,@EndDate datetimeUse:@StartDaysDiff int,@EndDaysDiff intIn the front end app take the desired date and do a DATEDIFF with thecurrent date, then pass the date diff as a parameter as an integerrather than deal with the dates at all.Then...DECLARE @TodayDate datetime, @StartDate datetime, @EndDate datetimeSELECT @TodayDate = GETDATE()SELECT @StartDate = DATEDIFF(DAY,@StartDaysDiff,@TodayDate)SELECT @EndDate = DATEDIFF(DAY,@EndDaysDiff,@TodayDate)SELECTa.SomethingFROMdbo.Appointments aWHEREa.AppointmentDate BETWEEN @StartDate and @EndDateJust wondering ...Thanks,lq
I am having the "classical problem" of the forums; A local date changed to the american format (normally in the development environment) which I call "Switching months with days"... well it switches both again if you press "view report" again :)The problem I'm having is that when I Navigate from one report to other, the dates get switched... Everything's been set in the locale uk date format (dd/mm/aaaa), in the operating system, in the database and in the reports... and the reports are working properly through the web interface.. the only thing that is not working properly is the Navigation which switches the date month with the day...Changing all the Reporting Services server, database server to the american format date is not an acceptable solution. Thanks for any guidance on this, as I'm pretty lost...Jose
My basic goal is to try to simplify inputs for the user. I have 3 parameters: Begin Date, End DAte and Duration. Duration will contain 3 choices: All, 2 Years and Range and is meant to give them a shortcut to dates as described below:
All - Would automatically populate the start date to 10/01/2005 and an end date to current date
2 Years - Would automatically populate the start date to current date minus 2 years, and the end date to current date.
Range - Would allow the user to select any dates as desired.
I'm able to get the dates to populate based on the duration field using non-queried values based on the Duration value, but the problem is that if I want to allow them to select Range the calendar control is not available and a text box is displayed.
I've tried to create some code in the properties that would populate, but I keep getting that this item is Read Only. The code I've created is as follows:
public function populateDates(Duration) as String
Select Case Duration Case = "Range" Report.Parameters!pBeginDate = Report.Parameters!pBeginDate Report.Parameters!pEndDAte = Report.Parameters!pEndDAte Case = "All" Report.Parameters!pBeginDate = #10/01/2005# Report.Parameters!pEndDAte = Now().Today case = "Two" Report.Parameters!pBeginDate = DateAdd("yyyy", -2, Now().Today) Report.Parameters!pEndDAte = Now().Today end select end sub
My only goal is to give the User the 3 choices, but still keep the calendar control available, and I can't seem to do this?
have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003.
i have tried with the following query, i need help putting my queries together select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004')
when i run this query i get this message
Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15
Hello all, I have two mult-value parameters in my report. Both of them working with selecting one or more values. But, when I test using "(Select All)" values for both parameters , only one parameter works. The "available values" for these two parameters are both from the data set.
select distinct ProductType from Product order by ProductType
We are using ReportServer URL Access. The parameters aren't pass into the URL, they have a default value and it's possible to change them by the "parameter toolbar". We are using datetime parameters with the very usefull calendar...
We are using a French version of : Windows (XP or W2K3), SQL2005+SP1 and VS2005 + SP1. It works fine, the calendar (for datetime parameter) is in French.
After installing the French SP2, the calendar is in English (w2k3 + VS2005 SP1). On a new installation (XP), with SQLExpress SP2 Advanced + Toolkit (in French), we have the same problem.
With this URL (solution 1), the calendar is in English (it was in French before the SP2):
There are several parameters on a report. One of the parameter is a multi-select enabled parameter and I suppressed the value "All" showing as one of the item in the drop down list, simply by filter out the [bha].[bha].CURRENTMEMBER.LEVEL.ORDINAL to 1, as "(Select All)" is pre-assigned to the drop list when multi-select is enabled and it is confusing to show "(Select All)" and "All" in the drop list. However I have another report which is linked to this report and the value which is required to pass to this report for this parameter is "All". Can I pass the "Select All" as a parameter from the other report? If so, how? Thanks.
I am trying to select all records added between 2 dates that the user inputs into a form and am having problems. I had this working no problems with asp but can't seem to get it working with .net. BTW I am using SQL Server and Visual Studio.
The asp.net code I am trying to use is:
Me.SqlSelectCommand1.CommandText = "SELECT news_title, news_date, news_type, news_link FROM news WHERE (news_type = 'news') AND (news_date BETWEEN CONVERT(DATETIME, '"" & startdate & ""', 102) AND CONVERT(DATETIME, '"" & enddate & ""', 102))"
....
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim startdate As DateTime startdate = Request.Form("date_from")
Dim enddate As DateTime enddate = Request.Form("date_to")
right now I am able to retrieve the month and year from a field by month(TS_Date), Year(TS_Date), my question is can I put both results as one so output can say Date 2 2008 instead of two columns with month 2 year 2008. any help would be appreciated
I have a form that's filtering search results and porting to a gridview. The drop down is selecting from the date column of SQL, but i want it the default value to show all dates in the gridview. In a normal string field, you can just use "%", but a datetime field won't take this. What can i use to show all dates?
Hi all,Right now I have this SELECT statement:SELECT MUSIC_PK, MUSIC_TITLE, MUSIC_TITLE2, MUSIC_ORIGINAL_SINGER Now I want to add a second SELECT statement (same as above, but with an additional column) if the record was added within the 10 days (while still select other records, I just need to select an additional column). How would I do that? The additional column that I want to select is called MUSIC_NEW (so I can display if the record is new within 10 days.) I have a column in my table named DATE_ADDED, how do I go to calculate if it's within the 10 days since the day it was added to the current time?Thank you very much,Kenny.
I'm using the designer to create/modify strongly typed datasets. I have one select statement that I'm having considerable trouble with. The user selects search parameters from a form. These search parameters are used to filter the data sent to a gridview control. Three of these parameters are almost enough to make me abandon the dataset in this case.The first two are minimum and maximum age. I have a birth date field, but not an age field - ages have a habit of changing without user intervention <grin> and can be calculated. In ASP I'd do something like this:strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') >= " & strLowerAge & ") AND (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")" I can't figure out how to get datediff working in the designer much less testing against upper and lower age limits.The third parameter is astrological sign. Again, I calculate it based on the birth date. I don't have a field for it. I could, but I would have to modify all the pages that add or edit records to the database to insure all the records that have birth dates also have the right sign. I'm leaning in that direction, but is it possible to accept a sign as a parameter and calculate, based on the birth date, which records qualify? I need to get the age issue fixed. The sign is a nice to have, since I can do it another way if I have to. BTW: I did decide to abandon the dataset in favor of a SqlDataSource control. This allowed me to build my select string in the code behind the way I would have in ASP. This resulted in paging and sorting not working properly. Sorting would be nice, paging is necessary. I'm pretty sure going back to the dataset or using a stored procedure would fix the paging problem, and I've yet to work with stored procedures (it's on my list of things to learn). Any comments or feedback on this would be avidly read and appreciated.Diane
ID PID From To Code 1 1 14/02/07 17/02/07 X 2 1 17/02/07 19/02/07 X 3 1. 19/02/07 23/02/07 E 4 1 26/02/07 28/02/07 X 5 1 1/4/07 1/5/07 E 6 2 01/03/07 03/03/07 X 7 2 04/03/07 10/03/07 X 8 2 10/03/07 14/03/07 E
Result
ID PID Date 4 1 26/02/07 7 2 04/03/07
I want to be able to create a select query on the above table. The table will show ID, PersonID (PID), From and to date, and code. If the code is X then the next €˜from record€™ should be the same date as the €˜to date€™. If the code is E then the next €˜to€™ date can be anytime after the previous €˜to€™ date. I want to be able to report on all record where there is a day difference between the previous €˜to€™ date. I.e. ID 4 and 7 €“ the previous records both have an X and there is at least a days difference between the dates.
I have a table with addresses and activity dates. I need to be able to retrieve the past 3 activity dates and see if the first and last occurred within 15 days. If so, I need to flag them.
Using max date gets me the last date but not the previous two. I was trying to use top 3 in desc order and that didnt seem to work either.
Hello. I'm having troubles with a query that (should) return all therecords between two dates. The date field is a datetime type. The db isSQL Server 2000. When I try thisSELECT RESERVES.RES_ID, PAYMENTS_RECEIVED.PYR_ID,PAYMENTS_RECEIVED.PYR_VALUE, PAYMENTS_RECEIVED.PYR_DATE,CUSTOMERS.CUS_NAMEFROM RESERVES LEFT OUTER JOINPAYMENTS_RECEIVED ON RESERVES.RES_ID =PAYMENTS_RECEIVED.RES_ID LEFT OUTER JOINCUSTOMERS ON RESERVES.CUS_ID = CUSTOMERS.CUS_IDWHERE (PAYMENTS_RECEIVED.PYR_DATE >= '2006-03-20 00:00:00') AND(PAYMENTS_RECEIVED.PYR_DATE < '2006-03-27 00:00:00')on a "query builder" in visual studio, I get the results that I want.But when I use exactly the same query on an asp 3 vbscript script, Iget no results (an empty selection).I've done everything imaginable. I wrote the date as iso, ansi, britishformat using convert(,103) (that's how users will enter the dates),i've used cast('20060327' as datetime), etc. But I can't still get itto work. Other querys from the asp pages work ok. Any ideas?thanks a lot in advance
I have a report that uses several filters including a from and to date filter, I have a field named TimeLastMod which is a Timestamp. I am extracting the From and To dates from this field using Select format(TimeLastMod,'MMM') as FromMonth,format(TimeLastMod,'MMM') as ToMonth From Eventlogs syntax.
These I pass on to a parameter and eventually to a tablix filter in an between opeartion. When I run the report, the records returned by the report is not what is expected as it sometimes returns an extra months data e.g., using between Aug and Sept will return october data as well.
Hi I have a DropDownlist (Drop1) and a GridView,the GridView is bount to an SqlDataSource1 that has 2 Select parameters CatId and SourceId The dropdownlist has a selectedvalue of the following format 15-10(2 numbers seperated by -).I want to set CatId to 15 and SourceId to 10 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Art %>" SelectCommand="Select * from Option Where SourceId=@SourceId And CatId=@CatId"> <SelectParameters> <asp:ControlParameter ControlID="Drop1" Name="SourceId" /> <asp:ControlParameter ControlID="Drop1" Name="CatId" /> </SelectParameters> </asp:SqlDataSource> Can anyone help me to define the parameters? thanks
I have a form that has 4 fields to fill in. I have a button that can add these fields to a sql database. I also want to put a button next to the "add" button so you can fill out the same fields and search for those values. Here's what i have so far. I want to select based on the fields, but i'm having trouble with the syntax of the parameters. I also want to add something, so if nothing is filled out in one of those boxes, it retuns back all records for the default value. string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString; SqlConnection myConnection = new SqlConnection(strConnection);
String selectCmd = "SELECT * FROM users WHERE firstname = @firstame or lastname = @lastname or office = @office or team = @team"; SqlDataAdapter myCommand = new SqlDataAdapter(selectCmd, myConnection);
Im enabling an apllication to use ODBC to connect to sqlserver which currently uses Oracle OCI, i have no prior knowledge about odbc use. Im unsure how to approach where clause parameters (bind parameters) ie Oracle OCI select name into :name from emp where name = :a_name
all seems ok with sqlbindparameter, but sqlexecute fails with sqlstate 22001, String data, right truncation. The question: can i use ? parameter in where conditions, if not whats the best approach. Many Thanks.
I know that when you select Multi from the parameter report properties, that it automatically makes "Select All" Available. So how would i take off the "All Value". My users dont want to see All, if Select ALL is available.
How do i exclude from "All" from my report parameter/dataset??
In multivalue parameters by default an option "All" was appearing. You can select both "All" and any of the individual parameters. However the latest report I created suddenly appeared with "(Select All)" as well as "All". (Select All) actually selects (or deselects) all items.
Does anyone know where (Select All) came from, is this a new thing in SP2? How do you control it.
Also, is there a functionality where you can select "All", but if you then select another paramater it automatically deselects "All"?
