CODEWITHSUNDEEP
sqloracle-databasesqlplus
user3191460
user3191460

Reputation: 43

User Input Query

I'm having some issues with this Query. I am trying to create a query where the user will be able to input 2 dates at runtime. It's asking me for an input, for both start date and end sate, but, it then comes up with: ORA-00904: "JAN": invalid identifier

My Query is currently:

SELECT p.FirstName, p.email, a.Avatar_Name, s.Species_Name, a.Avatar_Level, a.Skill, s.Wisdom_Level, a.AvA_DOB, a.Hoard
    FROM Players P, Avatars A, Species S
    WHERE a.Player_ID = p.Player_ID
    AND a.Species_ID = s.Species_ID
    AND Avatar_Level = 'Master'
    AND AvA_DOB BETWEEN &startdate AND &enddate
    ORDER BY Hoard DESC;

I want the user to be able to find a list of avatars between the two dates the user enters. Any help please? If more information is needed please do ask! (: (SQL PLUS)

Upvotes: 0

Views: 89

Answers (1)

APC
APC

Reputation: 146249

Date values in SQL need to be wrapped in single quotes. Oracle will cast these strings to a date datatype, but it's safer to use an explicit conversion, with the expected format mask.

SELECT p.FirstName, p.email, a.Avatar_Name, s.Species_Name, a.Avatar_Level, a.Skill, s.Wisdom_Level, a.AvA_DOB, a.Hoard
    FROM Players P, Avatars A, Species S
    WHERE a.Player_ID = p.Player_ID
    AND a.Species_ID = s.Species_ID
    AND Avatar_Level = 'Master'
    AND AvA_DOB BETWEEN to_date('&startdate', 'dd-mmm-yyyy') 
                AND to_date('&enddate', 'dd-mmm-yyyy')
    ORDER BY Hoard DESC;

Upvotes: 1

Related Questions