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

Want To Use Parameters To Filter For Dates Between Two (parameter, User-input) Dates

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

Look Up Renewals Dates In An Edit Parameters Form

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

Passing DateDiff Rather Than Actual Dates As Parameters

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

Problem With Dates Format Passed As Parameters...

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

Populate Dates For Parameters Based On A String Parameter

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

Date Select Query - Select Between Two Dates

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

(Select All) In Multi-select Enabled Drop Down Parameters Doesn't Work

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

ReportServer URL Access : French Language Problem For Parameters (dates) After Installing SQL 2005 SP2

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

(Select All) In Multi-select Enabled Drop Down Parameters

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

Select Dates Using BETWEEN?

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

Select With Max And Min Dates

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

SQL Select Help Needed - Dates

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

Select All Records Between 2 Dates

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

Select With Consecutive Dates

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

SELECT And Dates Question

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

Select Statement With Dates

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

Select All Dates From SQL Datetime Field

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

SELECT From A Specific Range Of Dates?

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

Getdate() To Select Dates Less Than 15 Months

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

Transact SQL :: Select Conditional Dates

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

How To Select All The Dates Of Month Even Though There Are No Records?

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

Problem With Dates In Dataset Select Statement

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

Stuck On Select Query Involving Dates

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

T-SQL (SS2K8) :: Select Past 3 Dates And Determine If 1st And 3rd Are Within Timeframe?

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

Select Records Between Dates - Query Works In VS 2005 But It Doesn't In Asp 3

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

Reporting Services :: Select Between From And To Dates In Multiple Parameter Report

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

Define Select Parameters

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

Select Parameters With Gridview

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

Odbc Select Where Parameters

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

Select With Date Parameters

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

Remove All And Keep Select All From Parameters

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

SSRS Parameters : Select All

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







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