DATE Datatype confusion?!@? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DATE Datatype confusion?!@?

Author

Topic

jeffb
Starting Member

2 Posts

Posted - 2007-12-17 : 19:45:38

I am having trouble inserting date values into my table. The error is SP2-0552: Bind variable "23" not declared. It is confusing the colons in the DATE datatype with bind variables.

CREATE TABLE event(
e_name VARCHAR2(20) NOT NULL,
c_name VARCHAR2(20) NOT NULL,
description VARCHAR2(110) NOT NULL,
location VARCHAR2(20) NOT NULL,
begin_date DATE NOT NULL,
end_date DATE NOT NULL,
CONSTRAINT pk_event PRIMARY KEY (e_name),
CONSTRAINT fk_event FOREIGN KEY (c_name)
REFERENCES committee (c_name)
);

INSERT INTO event VALUES ('Pizza Party', 'Marketing', 'Promotes society and free pizza', 'BMU', 31-DEC-2007:21:00:00, 31-DEC-2007:23:00:00);

I thought I was using the standard DATE format, whcich is DD- MON-YY HH:MI:SS

Please help...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-17 : 19:51:49

You need to put single quotes around your date/time data, just like you are doing with your strings. I think you'll need to remove the colon between the year and hour too (put a space there instead).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jeffb
Starting Member

2 Posts

Posted - 2007-12-17 : 21:19:31

I tried that, but it gives me this: ERROR at line 1:
INSERT INTO event VALUES ('Pizza Party', 'Marketing', 'Promotes society and free pizza', 'BMU', '31-DEC-07 21:00:00', '31-DEC-07 23:00:00')
*

ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-17 : 21:31:17

You posted the issue in wrong place, this is sql server forum.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-17 : 21:32:54

This is a Microsoft SQL Server website.

- Jeff
http://weblogs.sqlteam.com/JeffS

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-17 : 23:12:42

try:

http://www.orafaq.com
http://www.dbforums.com/forumdisplay.php?f=4
http://forums.oracle.com

elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-18 : 01:41:43

quote:
Originally posted by jeffb

I tried that, but it gives me this: ERROR at line 1:
INSERT INTO event VALUES ('Pizza Party', 'Marketing', 'Promotes society and free pizza', 'BMU', '31-DEC-07 21:00:00', '31-DEC-07 23:00:00')
*

ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

Note that in ORACLE the default date format is DD-MON-YYYY
If you want to express datevalue with different format directly in the INSERT statement, you need to use to_date function.

So '31-DEC-07 21:00:00' should be to_date('31-DEC-07 21:00:00','dd-mon-yyyy hh:mm:ss')

But as said post your question at ORACLE forums for better answers

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources