Select Parameters With Dates
Jan 16, 2007
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]"
SqlDataSource1.SelectCommand = SelCmd
SqlDataSource1.SelectParameters.Clear()
Dim parFilterProvider As New ControlParameter()
parFilterProvider.ControlID = "ddlProvider"
parFilterProvider.Name = "AssignedTo"
parFilterProvider.Type = TypeCode.String
SqlDataSource1.SelectParameters.Add(parFilterProvider)
Dim parFilterHospital As New ControlParameter()
parFilterHospital.ControlID = "ddlHospitals"
parFilterHospital.Name = "HospitalID"
parFilterHospital.Type = TypeCode.String
SqlDataSource1.SelectParameters.Add(parFilterHospital)
Dim parFilterStatus As New Parameter()
parFilterStatus.Name = "crtd_datetime"
parFilterStatus.DefaultValue = selDate.ToShortDateString
parFilterStatus.Type = TypeCode.DateTime
SqlDataSource1.SelectParameters.Add(parFilterStatus)
Gridview1.databind()
Any ideas?
View 5 Replies
ADVERTISEMENT
Mar 2, 2006
SQL 2005 Dev
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?
View 3 Replies
View Related
Dec 8, 2006
Hello Everyone and Merry Christmas
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)
View 2 Replies
View Related
Nov 29, 2005
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
View 9 Replies
View Related
Jan 8, 2007
Hi,
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
View 4 Replies
View Related
Jul 2, 2007
Hello,
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?
suggestion please!
Thanks!
Maureen
View 6 Replies
View Related
Aug 22, 2006
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
please help
mustfa
View 6 Replies
View Related
Apr 29, 2008
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
Any suggestion? thx
View 12 Replies
View Related
Mar 3, 2007
Hello,
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):
http://localhost/Reportserver$SQLExpress?%2fProjet+de+rapport1%2fReport1&rs:Command=Render
But with this URL (solution 2), the calendar is in French :
http://localhost/ReportServer$SQLExpress/Pages/ReportViewer.aspx?%2fProjet+de+rapport1%2fReport1&rs%3aCommand=Render
In the documentation, we have to use solution 1 (but we have a problem). Is the solution 2 a possible "alternative" ?
Thanks for your help,
Frederic
View 2 Replies
View Related
Feb 27, 2008
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.
View 1 Replies
View Related
Nov 5, 2007
How would I go about selecting a datetime field for a date range?
e.g.
$start_date = 04/11/2007
$end_date = $05/11/2007
I would have thought it would be something like this:
SELECT * FROM order_details BETWEEN $start_date AND $end_date
BUt I cannot get it to work in MS SQL? Would it be anything to do with the data stored such as 5/11/2007 12:00:00 AM
View 8 Replies
View Related
Nov 22, 2014
I´m struggling to get the following query:
Sales for the Last day of the month – sales for the last day of the previous month
Grouped by type and by month
DECLARE @sales TABLE
(type VARCHAR(10) NOT NULL,
date1 DATE NOT NULL,
sales NUMERIC(10, 2) NOT NULL);
INSERT INTO @sales(type, date1, sales)
[Code] ....
View 1 Replies
View Related
Mar 15, 2004
Hi everyone,
I've got a problem that I've been unable to work out.
I'm wanting to count the number of people that fall into a specific age group. For simplicity, lets say I'm JUST after people that are 10 years old.
This is what I'm trying to do:
SELECT 'Aged 10 years old', count(*)
FROM [People]
WHERE ([DOB] + [Date Entered]) = ?5 years?
Does anyone have a solution to this?
Thanks
Andrew
View 2 Replies
View Related
Apr 6, 2004
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")
SqlDataAdapter1.Fill(DataSet1)
Repeater1.DataSource = DataSet1
Repeater1.DataBind()
End Sub
With this I am getting the following error:
"Syntax error converting datetime from character string. "
So I am assuming it is something to do with the way I am getting the date from the form as when I hardcode the dates in it works???
Any help would be greatly appreciated, thanx
View 1 Replies
View Related
Jul 30, 2012
I have a table with
EmpNum, Date, Abstype
What I want is to pull a list of all the employees and the Monday date of employees who have an absence on a Monday --> Friday consecutively.
eg Table
EmpNum, Date, Abstype
001 07/23/2012 VAC *Monday
001 07/24/2012 VAC
001 07/25/2012 VAC
001 07/26/2012 VAC
001 07/27/2012 VAC
003 07/23/2012 VAC * Monday
[code]...
As these are the 2 that run from monday-friday
View 2 Replies
View Related
Sep 30, 2007
My table contains three records as an example
item boxes quantity date
CH 20 16 10/09/2007
CH 10 20 11/09/2007
CH 15 16 12/09/2007
using the request:
SELECT item, sum(boxes), quantity FROM transactions WHERE (item = 'CH' AND date_recvd BETWEEN '09-09-2007' AND '12-09-2007') GROUP BY item, quantity
I get:
CH 35 16
CH 20 10
but if I change my date parameters to:
SELECT item, sum(boxes), quantity FROM transactions WHERE (item = 'CH' AND date_recvd BETWEEN '09-09-2007' AND '10-09-2007') GROUP BY item, quantity
I still get:
CH 35 16
CH 20 10
My query syntax is obviously incorrect. What should it be please?
View 5 Replies
View Related
Feb 6, 2008
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
View 2 Replies
View Related
Aug 3, 2007
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?
View 3 Replies
View Related
Aug 29, 2007
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.
View 8 Replies
View Related
Apr 7, 2008
Hi,
I need help. I want to select dates less than 15 months, as i am new to sql server , can anyone advise me.
I tried this query but it gave me the dates that are greater than 15 months
Selec calendardate from table
where calendardate < getdate()-457
Thanks
View 3 Replies
View Related
Oct 15, 2015
TSQL statement, it should select the first date of the month at 11:59 PMÂ as Firstdate and Closedate as 10 days later at 11:59 PM.
View 14 Replies
View Related
Nov 28, 2007
I have one SQL Table with 2 columns as below
Column1: ProductionDate - DateTime - Not NULL
Column2: Quantity - Int - Not NULL
Now There are 2 Records in Table
1-1-2007, 5
1-3-2007, 7
Output of Result should be as below
1-1-2007 5
1-2-2007 0
1-3-2007 7
1-4-2007 0
1-5-2007 0
1-6-2007 0
.
.
.
1-31-2007 0
Means Query should return all the dates of Month with Quantity and if no entry in Table then 0 for Quantity.
How to Do it? Please suggest with Query
View 5 Replies
View Related
Mar 14, 2007
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
View 8 Replies
View Related
May 11, 2007
Table 1
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.
View 4 Replies
View Related
Sep 7, 2014
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.
View 9 Replies
View Related
Nov 9, 2006
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
View 1 Replies
View Related
Oct 26, 2015
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.
View 5 Replies
View Related
Apr 13, 2007
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
View 2 Replies
View Related
Aug 2, 2007
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);
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@firstname", SqlDbType.VarChar, 50));
myCommand.SelectCommand.Parameters.Add("@firstname", txtFirstName.Text);
myCommand.Parameters.AddWithValue("@lastname", txtLastName.Text);
myCommand.Parameters.AddWithValue("@team", dwnTeam.Text);
myCommand.Parameters.AddWithValue("@office", dwnOffice.Text);
DataSet ds = new DataSet();
myCommand.Fill(ds, "users");
MyDataGrid.DataSource = ds.Tables["users"].DefaultView;
MyDataGrid.DataBind();
View 5 Replies
View Related
Jan 6, 2004
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
via ODBC, connecting to sql server i'm attempting
select name from emp where name = ?
with,
sqlprepare
sqlbindparameter
sqlexecute
sqlbindcol
sqlfetch
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.
View 1 Replies
View Related
Oct 14, 2013
i have 3 table
DECLARE @a1 table(
p1 int ,
date1 smalldatetime
)
Insert Into @a1
(p1,date1)
Values
[code]....
how i get resulting table
num_p1 num_p2 num_p3
1 3 5
View 13 Replies
View Related
Jan 19, 2007
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??
View 2 Replies
View Related
Mar 12, 2007
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"?
Richard
View 6 Replies
View Related