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
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)
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
Hi, Table A member_no - character 5course1 - character 1course2 - character 1 Table Bcourse_no - character 1course_name - character 20I'd like a resultant view that contains A.member_no, A.course1, B.coursename as COURSE1NAME, A.course2, B.course_name as COURSE2NAMEI know how to make the join and pick up the name for course1 but I DON'T know how to pick up both names of course 1 and course 2 at the same time.I'm thinking I have to make a join on one column and then join the result of that a second time to pick up the other column's value? Thanks in advance for any assistance,Stewart
Hi, My users table contains a field called researchInterestId which looks like this: 1, 5, 10 This is because users where allows to select multiple options when choosing their research interests. I have another table which contains the names of those research interests, which looks like this: researchInterestId researchInterestName 1 Biology 2 Cancer
My question is, when selecting my list of users, i wish to also display the names of their research interests. I know how to inner join but im not sure in this case as there are multiple values (1, 5, 10) Hope that makes sense and that someone can point me in the right direction or let me know what this type of query is called? Thanks Sam
Hi, I am trying to do a simple select statement which returns data from the table. The criteria is only if the count of the records is more than 10. Say, if a vendor called test exists 10 times in the table, I would like to display that vendor only, if less than 10, then that vendor won't be displayed at all. Here is what I have started.
Select vendor_id, vendor_name, Invoice_Date, Invoice_Number from Vendors where Invoice_Date Between '01/01/2007' And '03/03/2008' Group By vendor_id, vendor_name, Invoice_Date, Invoice_Number Having Count(Invoice_Number) > 10 This doesn't seem to work. What am I doing wrong here?
Hi, I have 2 tables. Plan and User. In my User table I have the following columns UserId ClientId Login Password PlatformId 1 1 abc XXX 1 4 1 jcn XXX 2 20 1 djfd XXX 1 and the Plan table PlanId PlanName 1 abc plan 2 Plan etc.. and ihave a sproc that will return the all the plans based on the clientId but i want to also get all the plans that have PlatformID 1 for the same client. This is my Sproc.. SELECT NULL PlanId, '< All Plans >' PlanName
UNION
SELECT Distinct PlanId, PlanName FROM Plan cp Join User u on cp.ClientId = u.ClientId WHERE u.PlatformId = 1 AND cp.ClientId = @ClientId OR ( cp.ClientId = cp.ClientId AND @ClientId IS NULL )
ORDER BY PlanName ASC
But they dont filter it by PlatformId any help will be appreciated. Regards, Karen
Hi all, I need some help please with an select query. here's so scenario:3x Tables linked: Vehicles, Requests & ResultsTable Vehicles - vId, make, model, priceTable Requests - rId, name lastname, requestedmake, requestedmodelTable Results - vId, rId I am trying to do a select on the Vehicles table where vId is not already also in the Results table for an given rId What happens on the app: 1. dealers add cars on website - populate Vehicles tbl. 2. clients do a request for a vehicle - Populates Requests tbl. 3. dealers go page where they can asossiate their vehicles with requests - which populates the results tbl with both id's.All help is very much appreciated. RegardsJacques
I have worked on developing a project that I am pleased to say is comming along nicely considering I have only been doing programming for a few weeks but I am now stuck and may be going down the wrong path.
I have two tables one that is used for logging via username and password, it also holds a third coloumn realting to the suers account that they do not enter. Once they have logged in I am recalling the users identity and running a query to establish what account the user belongs to. Later on in the script I am trying to run a query on a table called orders that list all orders belonging to that account that was established in the earlier query labelled as 'accountrequesting'. The script I have below returns all orders for all accounts even though I have tried a wide variety of endings on the Sql search on the orders table.
The code may look a little untidy and to advanced programmers may look rough but I am still learning.....please can anyone help.
sub Page_Load(sender as Object, e as EventArgs)
'collecting the user identity Dim user as string user=request("User.Identity.Name")
'running a query to establish what account they have access to all from users table Dim accountrequesting as string accountrequesting = "select account_id from users where username = " & user & "'"
'opening the data source and connection Dim ConnectionString As String = "Data Source=xxxx;Database=xxxx;User ID=xxxxxxx;Password=xxxxxxx" Dim Myconn as New SQLConnection(connectionstring)
'this is the line I am stuck on, the account id in this orders table needs to match against the value gained in accountrequesting above tried a lot of different endings
Dim MySQL as string ="select prodshipped, account_id from orders"
What is the correct syntax for querying the first 20 characters of amemo field?I'm trying toSELECT CAST (varchar(20) fieldname) as newfieldnameand i get a bunch of errorsCan someone please help me?MS Access DB used with ColdFusionThanks
I have a table consisting of two fields, OStype and OSversion, withentries like:OStypeOSversionsolaris 2.5solaris 2.6redhat 6.2redhat 6.2solaris 8redhat AS4solaris 10solaris 10redhat AS2.1redhat AS3redhat AS4I want to create a select statement that returns for each OS type, thetotal number of entries and for each version the total number ofentries.In the example the result would be:OStype OStype Count OSversion OSversion Countsolaris 52.5 12.6 18 110 2redhat 66.2 2AS2.1 1AS3 1AS4 2Thanks in advance for your help.Ian
Hey guys, Hoping someone can help me ot here.. I am developing an SQL statement that can be run on DB2 and have got something already setup, but i need to modify it so that it only returns the highest record for a certain field.
let me explain/Show you what i have got, and what i want to acheive: The highlighted rows, is what i want to work with, as i only want to return the row with the highest value in ICPNLX (first Column)
Im hoping someone can help me out please, one person told me to use a sub-select query, but i am having trouble doing it.
SQL CODE:
Select icpnlx , IBPUSL, IBPUST, IBSUNO, XBXSMT, ICPUNO, IBITNO,ICPNLI, ICPNLS, ICREPN, IBRVQA-IBSDQA as REMAIN, MBWHSL FROM mvxbdtaCF3.MPLIND INNER Join mvxbdtaCF3.MPLINE On ICCONO = IBCONO And ICFACI = IBFACI And ICWHLO = IBWHLO And ICPUNO = IBPUNO And ICPNLI = IBPNLI And ICPNLS = IBPNLS INNER Join mvxbdtaCF3.XANLIN On ICPUNO = XBPUNO And ICPNLI = XBPNLI And ICPNLS = XBPNLS And IBCONO = XBCONO And IBFACI = XBFACI INNER JOIN MVXBDTACF3.MITBAL ON ibitno = mbitno and ibcono = mbcono and ibwhlo = mbwhlo WHERE (IBCONO = 200) And (IBFACI = '010') And (IBWHLO = '010') And (XBXSMT = '91730') AND IBITNO = '8552-6654' AND (ICREPN > 0) And (IBPUSL >= '50' And IBPUSL < '75')
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.
Hi,I have a need to create a table detailing the ID of all contacts and thelast time they were contacted. This information is stored in 2 tables,'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'in the 'activity' table).I guess I need some sort if iteration to go through each contact and findfind the last activity that took place against each of them (there many bemore than 1 activity against each contact) and then place the output valuesinto the new table.Can anyone show me how to go about this?Thanks!
Now each table holds different measures e.g. 326, 229 & 278. I would like to subtract the measure_value of 229 from 278 and then add the measure_value from 278 from this. e.g
(326-229) + 278 = new measure_value.
This is for each id-product where the following 3 conditions meet:
the ID_PRODUCT, ID_MARKET AND ID_BUCKET match. Im lost, any help would be great.
thanks
Jay
p.s this what i have started with at the moment as a test however it dont work.
SELECT ID_PRODUCT, ID_MARKET, ID_BUCKET, ID_COLLECTION, ID_MEASURE, MEASURE_VALUE FROM (SELECT TOP 100 PERCENT dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT, dbo.DPOUT_EXCEPTIONS_326.ID_MARKET, dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET, dbo.DPOUT_EXCEPTIONS_326.ID_COLLECTION, dbo.DPOUT_EXCEPTIONS_326.ID_MEASURE, dbo.DPOUT_EXCEPTIONS_326.MEASURE_VALUE + DBO.DPOUT_LSBP_229.MEASURE_VALUE as 'MEASURE_VALUE' FROM dbo.DPOUT_EXCEPTIONS_326 INNER JOIN dbo.dpout_lsbp_229 on dbo.dpout_exceptions_326.id_product = dbo.dpout_lsbp_229.id_product where dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT = dbo.dpout_lsbp_229.id_product and dbo.DPOUT_EXCEPTIONS_326.ID_MARKET = DBO.DPOUT_LSBP_229.ID_MARKET and dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET = DBO.DPOUT_LSBP_229.ID_BUCKET)
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'm very green with SQL so I could do with some advice please.
I need to pull some data from a table based on the year portion of a datetime field, so far I've got this...
USE MfgSys803
SELECT orderdate, ordernum FROM orderhed
WHERE ((SELECT CONVERT(VARCHAR(4),GETDATE(),111)) = (SELECT CONVERT(VARCHAR(4),ORDERDATE,111) FROM orderhed))
... the field 'orderdate' is the datetime. The purpose of the WHERE statement is to get the current year fromt he system and then compare this to the current year of the field 'orderdate'.
Unfortunately I get the error...
Msg 512, Level 16, State 1, Line 3 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
... the 'SELECT CONVERT' portions of the WHERE work fine on thier own but I can't use them together.
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.
I have 2 SQL 2000 servers. One has been added as a linked server. They both have a db called claims. If I am on server 1 and want to write a sql statement I am stuck with the write syntax
I tried select patient.* from testedi..claims.
The server is linked using the sa user. I know this is simple but in a hurry and stuck. THe servers are on same domain. Thanks
With the OLEDB source, is it wrong to use a table / view as a source and only check the columns required or is it beneficial to write a select col1, col2 etc etc as a SQL command?
I cannot see any difference in performance between the two.
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