CODEWITHSUNDEEP
sqlsql-server-2008datetimecoldfusioncoldfusion-9
Onebigcelt
Onebigcelt

Reputation: 17

Trouble INSERT INTO string value into DATETIME msSQL datatype

I have looked all over the place and I can't find the answer.

Here is the error I am getting using ColdFusion and MsSQL

Error Executing Database Query.

[Macromedia][SQLServer JDBC Driver][SQLServer]Conversion failed when converting date and/or time from character string.

The error occurred in SQL

INSERT INTO Requests ( 
    RequestName, 
    RequestEmail, 
    RequestPhone, 
    RequestType, 
    RequestSiteSection, 
    RequestDue, 
    RequestUrgent, 
    RequestTitle, 
    RequestURL, 
    RequestMessage, 
    Attachment1, 
    Attachment2, 
    Attachment3, 
    Attachment4, 
    Attachment5, 
    RequestCreated, 
    RequestModified, 
    RequestPriority, 
    RequestAssignedTo, 
    RequestStatus 
) 
VALUES ( 
    '', 
    'Joshua >Davis', 
    '[email protected]', 
    '8046789', 
    'Correction', 
    'Natural Heritage', 
    '09/11/2013', 
    'This is the Title of the REQUEST!', 
    'http://www.dcr.virginia.com', 
    'rewas', 
    '/opt/coldfusion8/runtime/servers/coldfusion/SERVER-INF/temp/wwwroot-tmp/neotmp47128.tmp', 
    '', 
    '', 
    '', 
    '', 
    (param 1), 
    (param 2), 
    '0', 
    'Webmaster', 
    'Pending' 
)

Each param is cfqueryparam cfsqltype='CF_SQL_timestamp' value='#CreateODBCDateTime(now())#'

this error comes up when I try to insert using any of the following

I have tried concatenating time and date together, cast and convert to datetime , formating the date and time but no luck I am stuck.

I appreciate your help.

here is the query

INSERT INTO Requests (
    RequestName,
    RequestEmail,
    RequestPhone,
    RequestType,
    RequestSiteSection,
    RequestDue,
    RequestUrgent,
    RequestTitle,
    RequestURL,
    RequestMessage,
    Attachment1,
    Attachment2,
    Attachment3,
    Attachment4,
    Attachment5,
    RequestCreated,
    RequestModified,
    RequestPriority,
    RequestAssignedTo,
    RequestStatus
)
VALUES(
   '<cfif IsDefined('form.requestUrgent')>#form.requestUrgent#</cfif>',
   '#form.requestName#',
   '#form.requestEmail#',
   '#form.requestPhone#',
   '#form.requestType#',
   '#form.requestSection#',
   '#form.requestDueDate#',
   '#form.requestTitle#',
   '#form.requestURL#',
   '#form.requestMessage#',
   '#form.attachment1#',
   '#form.attachment2#',
   '#form.attachment3#',
   '#form.attachment4#',
   '#form.attachment5#',
   getdate(),
   getdate(),
   '0',
   'Webmaster',
   'Pending'
)

You can sub out the getDate() with any of the above options #var# convert cast, param, etc

Upvotes: 1

Views: 1454

Answers (1)

Miguel-F
Miguel-F

Reputation: 13548

From the comments

The columns and values from your insert query seem to be off in your example code. Your insert begins with the column RequestName but the first variable in your VALUES clause is form.requestUrgent and then form.requestName etc. Because of this your query is attempting to insert form.requestSection ('Natural Heritage' in your example) into the date column RequestDue.

Excerpt from your example:

INSERT INTO Requests ( 
    RequestName,                /* column 1 */
    RequestEmail,               /* column 2 */
    RequestPhone,               /* column 3 */
    RequestType,                /* column 4 */
    RequestSiteSection,         /* column 5 */
    RequestDue,                 /* column 6 */
    RequestUrgent,              /* column 7 */
    RequestTitle,               /* column 8 */
....


VALUES (
   '<cfif IsDefined('form.requestUrgent')>#form.requestUrgent#</cfif>', /* value 1 */
   '#form.requestName#',        /* value 2 */
   '#form.requestEmail#',       /* value 3 */
   '#form.requestPhone#',       /* value 4 */
   '#form.requestType#',        /* value 5 */
   '#form.requestSection#',     /* value 6 */
   '#form.requestDueDate#',     /* value 7 */
   '#form.requestTitle#',       /* value 8 */
....

And I cannot stress enough you should be using <cfqueryparam> tags for ALL of your variable values. It helps protect against SQL injection and also improves query performance.

Upvotes: 5

Related Questions