mike55 Posted September 29, 2004 Posted September 29, 2004 Hi all Am having a problem with the following sql statement... The line that is giving the problem is: ((Scan.ScanDate) between "+dateA+" and "+dateB+") When i remove this line i get all the data i need, but when i add it in no data is returned, now i have verified that the two dates that i am entering are in the table. Would appreciate any suggestions Mike55 Select Student.SchoolNum, School.SchoolName, Student.CourseName, Student.MartID, Student.StudentId, Student.StudentNum, Student.Forename, Student.Surname, Scan.ScanDate, Scan.Reason From (Student inner join scan on student.MartID = Scan.MartID) innerjoin school on student.SchoolNum = school.SchoolNum where ((School.MartSchoolID) >= "+value+" and (School.MartSchoolID) <= "+value+") and ((Scan.ScanDate) between "+dateA+" and "+dataB+") and ((Scan.Reason) = 'Absent' or (Scan.Reason) = 'Suspended') Quote A Client refers to the person who incurs the development cost. A Customer refers to the person that pays to use the product. ------ My software never has bugs. It just develops random features. (Mosabama vbforums.com)
kejpa Posted September 29, 2004 Posted September 29, 2004 If ScanDate is a date field you need to use # around the dates supplied ((Scan.ScanDate) between #"+dateA+"# and #"+dataB+"#) HTH /Kejpa Quote
mike55 Posted September 29, 2004 Author Posted September 29, 2004 If ScanDate is a date field you need to use # around the dates supplied ((Scan.ScanDate) between #"+dateA+"# and #"+dataB+"#) HTH /Kejpa Thanks man, Had just solved the problem before looking at this posting. Is it possible to do the following type of insert statement in SQL Server 2000 insert into table values (Do a nested select), 0,0,0 i.e. insert into table1 values (Select col1, col2 from table 2),0 ,0 ,0 ????? Mike55 Quote A Client refers to the person who incurs the development cost. A Customer refers to the person that pays to use the product. ------ My software never has bugs. It just develops random features. (Mosabama vbforums.com)
Joe Mamma Posted September 29, 2004 Posted September 29, 2004 insert into table1 Select col1, col2, 0 ,0 ,0 from table 2 Quote Joe Mamma Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized. Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
mike55 Posted September 30, 2004 Author Posted September 30, 2004 In relation to using #, SQL Server throws an error saying that it is an unrecognised character. Mike55 Quote A Client refers to the person who incurs the development cost. A Customer refers to the person that pays to use the product. ------ My software never has bugs. It just develops random features. (Mosabama vbforums.com)
eramgarden Posted September 30, 2004 Posted September 30, 2004 http://www.sqlservermag.com is a great site for getting sql answers if u dont find your answer here. Quote
Administrators PlausiblyDamp Posted September 30, 2004 Administrators Posted September 30, 2004 SQL Server doesn't use the # symbols on dates - try it without them, also you may have better luck if you converted the code to a stored procedure and just passed the dates in as parameters. Quote Posting Guidelines FAQ Post Formatting Intellectuals solve problems; geniuses prevent them. -- Albert Einstein
Joe Mamma Posted September 30, 2004 Posted September 30, 2004 user parameters for dates and forget about the format Quote Joe Mamma Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized. Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.