[thelist] SQL Server smalldatetime field problems

[Steve Cook]

motiveDateBooked is smalldatetime, but the value you are feeding it is a
LongDate ('Tuesday, July 10, 2001'). motiveDate seems to have the same
problem as well.

.steve


----------------------------------
   WapWarp - http://wapwarp.com
 Wap-Dev - http://www.wap-dev.net
 Cookstour - http://cookstour.org
----------------------------------

> -----Original Message-----
> From: Norman Beresford [mailto:n.beresford at anansi.co.uk]
> Sent: den 10 juli 2001 15:14
> To: thelist at lists.evolt.org
> Subject: [thelist] SQL Server smalldatetime field problems
> 
> 
> Hi all
> 
> I'm having some real problems at the moment with a simple 
> INSERT sql command
> on a site.  Basically I want to insert some form values into 
> a SQL Server
> table.  The problem I'm having is with the date/time fields.  
> I've four
> fields, which I initially set to smalltimedate (I only needed a time
> accuracy of 15mins).  I built the SQL statement using 
> formatDateTime to
> ensure that they were in the correct format. However I'm 
> getting an error
> message from it, and for the life of me I can't correct it.
> 
> I'm sure that there must be some easy solution to this 
> problem, but for the
> life of me I can't find it.  If anyone could help I would be 
> most gratefull.
> 
> Norman
> 
> 
> 
> The table:
> 
> 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 8 0 0 0  0   0
> motiveStart smalldatetime 8 0 0 0  0   0
> motiveFinish smalldatetime 8 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 8 0 0 0  0   0
> 
> 
> The ASP
> 
> thisSQL = _
> "INSERT INTO motivaction ( motiveTitle, motiveForename, motiveSurname,
> motiveOrganisation, motiveAddress, motivePostcode, motiveTelephone,
> motiveEmail, motiveContactMethod, motiveDate, motiveStart, 
> motiveFinish,
> motiveVenue, motiveGuests, motivePackageID, motiveDateBooked)" & _
> " VALUES ('" & bookingTitle & "', '" & bookingForename & "', '" &
> bookingSurname & "', '" & bookingOrg & "', '" & 
> bookingAddress & "', '" &
> bookingPostCode & "', '" & bookingTelephone & "', '" & 
> bookingEmail & "', '"
> & bookingContactMethod & "', '" & 
> formatdatetime(bookingDate,vbLongDate) &
> "', '" & formatdatetime(bookingTimeStart,VBShortTime) & "', '" &
> formatdatetime(bookingTimeFinish,VBShortTime) & "', '" & 
> bookingVenue & "',
> " & bookingNumbers & ", " & packageID & ", '" &
> formatdatetime(bookingDateBooked,vbLongDate) & "');"
> 
> 
> The SQL
> 
> INSERT INTO motivaction ( motiveTitle, motiveForename, motiveSurname,
> motiveOrganisation, motiveAddress, motivePostcode, motiveTelephone,
> motiveEmail, motiveContactMethod, motiveDate, motiveStart, 
> motiveFinish,
> motiveVenue, motiveGuests, motivePackageID, motiveDateBooked) VALUES
> ('Miss', 'Belinda', 'Beresford', 'Private Individual', '105 Houblon
> ROad<BR>Richmond<BR>Surrey', 'TW10 6DB', '020 2222 2222,
> 'n.beresford at anansi.co.uk', 'Email', 'Tuesday, September 10, 
> 2002', '21:00',
> '23:30', 'Back garden', 68, 11, 'Tuesday, July 10, 2001');
> 
> 
> The Error
> Microsoft OLE DB Provider for SQL Server error '80040e07'
> 
> Syntax error converting datetime from character string.
> 
> /motivaction/booked.asp, line 65
> 
> 
> 
> ---------------------------------------
> For unsubscribe and other options, including
> the Tip Harvester and archive of TheList go to:
> http://lists.evolt.org Workers of the Web, evolt ! 
>