RE: FN-FORUM SQL Server INSERT (easy question)

date posted 10th July 2001 13:29

Hi,

Unfortunately a time is not a valid datetime, if you see what I mean.
You need to specify a date for the time to be on...

So you could use:

'190000101 19:27'

or combine your fields to include the date with the times (and save a
whole column!)

I always use the ISO date string format 'yyyymmdd hh:mm:ss' so that when
you move from server to sever etc. you don't have to worry if the
administrator has bothered to set the default locale correctly, if it is
in English or French (for month names).... It is always wrong at 3am in
the morning.....

-Ken



-----Original Message-----
From: Norman Beresford [EMAIL REMOVED]
Sent: 10 July 2001 10:41
To: [EMAIL REMOVED]
Subject: FN-FORUM SQL Server INSERT (easy question)


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#);



============================================================

* Free listing for freelancers
* Free to advertise jobs
* Free jobs distribution service
* Free database of 1000 freelancers

Freelancers and Freelance Jobs
http://www.freelancers.net

To post to the Forum:
[EMAIL REMOVED]

To unsubscribe please email:
[EMAIL REMOVED]

If you have difficulties unsubscribing please email:
[EMAIL REMOVED]

To subscribe to the digest for this list or for further information
please visit:
http://www.freelancers.net/forum.html