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

date posted 10th July 2001 11:02

try passing the date as an unambiguous value.... i.e. instead of 02/07/01
pass '02 July 2001' - SQL sometimes interprets 02/07/01 as the '07 February
2001'... so when it comes to 31/07/01 - it's an invalid date format.

if you're using ASP just use formatdatetime(yourdatevalue,vbLongDate) - that
works for me.....

C.
----- Original Message -----
From: Norman Beresford [EMAIL REMOVED]
To: [EMAIL REMOVED]
Sent: Tuesday, July 10, 2001 10:40 AM
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
>