0 
Insert into booking values 
('EWKF2VN','Canada',TO_DATE(11/20/12,'DD/MON/YY'),
TO_DATE(1/10/18 4:00PM,'DD/MON/YY HH:MIAM'),
TO_DATE(1/10/18 6:30PM,'DD/MON/YY HH:MIAM'),
'B',125.00,Booked,'AC101',8521169618);

Not sure why I am getting this error

Bind Variable not declared

Any suggestions?

Improve this question
1
  • Following up on @mathguy's comment to the accepted answer below - the first argument to TO_DATE should be a character string. Enclose the values you've got in single-quotes and you're good to go. Best of luck. Commented Nov 19, 2017 at 17:59

2 Answers 2

Reset to default
2

This is your code:

Insert into booking
    values ('EWKF2VN', 'Canada', TO_DATE(11/20/12,'DD/MON/YY'),
            TO_DATE(1/10/18 4:00PM, 'DD/MON/YY HH:MIAM'),
            TO_DATE(1/10/18 6:30PM, 'DD/MON/YY HH:MIAM'),
            'B', 125.00, Booked, 'AC101', 8521169618
           );

The value Booked is undeclared.

I would write this as:

Insert into booking ( . . . ) -- explicitly list columns here
    values ('EWKF2VN', 'Canada', DATE '2012-11-20',
            TIMESTAMP '2018-01-10 16:00:00',
            TIMESTAMP '2018-01-10 18:30:00',
            'B', 125.00, 'Booked', 'AC101', 8521169618
           );

This lists the columns explicitly. And it uses the built-in keywords for providing date and timestamp constants.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I am also a big fan of using date and time literals (the syntax you show is for "literals" - there are no operators there, they are just boilerplate keywords used to mark literals). However, as you know, the DATE literal has no time-of-day component. And using TIMESTAMP to generate a date is "wrong", in that it generates the wrong data type. We should avoid implicit casts whenever we can. TO_DATE() is OK to use here, just use it correctly. Also: The error has nothing to do with booked (the error would be "invalid identifier"). Alex explained the "bind variable" error.
1

You’re getting that error because you’re doing this:

TO_DATE(1/10/18 4:00PM, 'DD/MON/YY HH:MIAM'),
TO_DATE(1/10/18 6:30PM, 'DD/MON/YY HH:MIAM'),

with the value you are trying to convert missing single quote enclosures; so the :00PM and :30PM are being interpreted as bind variables. Hence the error message, as they are indeed not declared.

So you could do:

TO_DATE(‘1/10/18 4:00PM’, 'DD/MON/YY HH:MIAM'),
TO_DATE(‘1/10/18 6:30PM’, 'DD/MON/YY HH:MIAM'),

but I’d prefer to use date/timestamp literals if possible, as Gordon shows. You’re also missing single quotes around the date-only value and Booked as Gordon also mentioned, and explicitly listing the column names is indeed a good idea.

Improve this answer

5 Comments

Alas, literals don't make sense for dates with time-of-day. The date literal has no time-of-day, and it is not OK to use a timestamp literal to create a date. (Implicit casts are evil; you could use an explicit cast, but then that would be more complicated - and less efficient - than calling to_date()).
"I’d prefer to use date/timestamp literals if possible" - sorry if i'm ignorant here. But why??
@KaushikNayak - function calls are expensive (they take time to process - especially if you have a function call for every row), and date and timestamp literals are easier to read. They are also more portable - fewer changes to make if you need to translate the query to another SQL dialect.
Those ‘literals’ are interpreted as calls to to_date or whatever under the hood anyway; you can see that in an execution plan. Agree cast is extra overhead, but might fall into the premature optimisation category depending on volume. Legibility and disambiguity (only one valid format; no 12/24hr or mm/dd and dd/mm options) may be an acceptable trade-off, Depends on circumstances though.
(Actually, I slightly retract that; date literals are handled with to_date() under the hood, but it looks like timestamp literals are not. So I guess you can compare cast(timestamp '...' as date) with to_date(...) here. I still don't think the performance difference will be a significant factor for a lot of applications, but for bulk operations with large volumes it may be.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.