How to add a datetime field in sql7? 1

[E3]

Insert Into auctionitem values
(1,'wolfenstein','PC',20.00,1,Jul 3 1991 12:00AM ,1,'Newest PC Game')

Line 2: Incorrect syntax near '3'.

This is how i set it up
CREATE TABLE [auctionitem] (
[auctionitem_id] [int] NOT NULL ,
[productname] [varchar] NOT NULL ,
[category_name] [varchar] (75) NOT NULL ,
[currentbid] [money] NOT NULL ,
[numbid] [int] NOT NULL ,
[closingdate] [datetime] NOT NULL ,
[bidincrement] [money] NOT NULL ,
[description] [text] NULL ,
CONSTRAINT PK_auctionitemid PRIMARY KEY CLUSTERED
(
auctionitem_id
)
)

 

[tlbroadbent]

You need single quotes around all string values including dates.

Insert Into auctionitem values
(1,'wolfenstein','PC',20.00,1, 'Jul 3 1991 12:00AM' ,1,'Newest PC Game') Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[E3]

insert into auctionitem values (1,'wolfenstein','PC',20.00,1,'1991-07-03 00.00.00' ,1,'Newest PC Game')

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 statement has been terminated.

 

[E3]

I m using MS SQL.
insert into auctionitem values (1,'wolfenstein','PC',20.00,1,'1991-07-03 00.00.00' ,1,'Newest PC Game')

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 statement has been terminated.


insert into auctionitem values (1,'wolfenstein','PC',20.00,1,TO DATE('1991-07-03 00.00.00', 'YYYY-Mm-DD hh.mm.ss') ,1,'Newest PC Game')
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TO'.

[This message was edited by E3 on February 19, 2002 at 11:37.]

 

[tlbroadbent]

The date string format is invalid. You now have periods (.) instead of colons ) in the time. Why? Use a proper date format and SQL will do the conversion just fine. To_date is not a valid T-SQL function. Use the Convert function when needed. In your case it isn't needed.

This should work.

insert into auctionitem values (1,'wolfenstein','PC',20.00,1,'1991-07-03 00:00:00' ,1,'Newest PC Game')

I recommend reading SQL BOL about the date time formats and functions of T-SQL and the Convert fnction. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com