SQL Server INSERT (easy question)

date posted 10th July 2001 10:38

Hi all

I know this is a really simple question but I just can't find the answer to
it anywhere, possibly a case of wood and trees. I'm trying to insert data
into a SQL Server table. However I'm having real trouble with the syntax
for inserting dates and times. I've tried using #07/07/01# which works fine
in Access, but that doesn't work in SQL Server. Putting the date in single
quotes seems to work, although I think that there's some converting going
on, but it doesn't appear to work for times. Could anyone point out what
I'm doing wrong, or (and possibly better) point me to some information on
this? I've included the table information and the SQL statement at the
bottom of this email

Thanks in advance

Norman

The table loooks like this:

motiveID int 4 10 0 0 1 1 1 0
motiveTitle varchar 50 0 0 0 0 0
motiveForename varchar 50 0 0 0 0 0
motiveSurname varchar 50 0 0 0 0 0
motiveOrganisation varchar 50 0 0 0 ('Private Individual') 0 0
motiveAddress varchar 250 0 0 0 0 0
motivePostCode varchar 10 0 0 0 0 0
motiveTelephone varchar 50 0 0 0 0 0
motiveEmail varchar 50 0 0 0 0 0
motiveContactMethod char 4 0 0 0 0 0
motiveDate smalldatetime 4 0 0 0 0 0
motiveStart smalldatetime 4 0 0 0 0 0
motiveFinish smalldatetime 4 0 0 0 0 0
motiveVenue varchar 50 0 0 0 0 0
motiveGuests int 4 10 0 0 0 0
motivePackageID smallint 2 5 0 0 0 0
motiveDateBooked smalldatetime 4 0 0 0 0 0

The SQL statement looks like this:

INSERT INTO motivaction ( motiveTitle, motiveForename, motiveSurname,
motiveOrganisation, motiveAddress, motivePostcode, motiveTelephone,
motiveEmail, motiveContactMethod, motiveDate, motiveStart, motiveFinish,
motiveVenue, motiveGuests, motivePackageID, motiveDateBooked)
VALUES ('Miss', 'Anne', 'Cooper', 'Private Individual', '157 Houblon
RoadRichmondSurrey', 'TW10 6DB', '020 2222 2222,
[EMAIL REMOVED] 'Email', #22/November/2001#, #18.00#, #23.30#,
'My house', 45, 9, #7/10/01#);