Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Add Date/time field to SQL database

Posted on 2011-02-28
4
Medium Priority
?
602 Views
Last Modified: 2012-05-11
I'm tryin to add a datetime field using below CF statements but I get the following error message.

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]String or binary data would be truncated


<cfquery name="rs" datasource="test">
insert into log(userid, name, type, tdate)
values('#userid#', '#name#', 'Login', '#Dateformat(Now(),'m/d/YYYY')# #Timeformat(Now(),'h:MM:SS TT')#')
</cfquery>

The weird thing is that when I copy the sql statements thrown by sql and run it in  SQL Server Mgmt studio, the record is added without any problem.

sql;
insert into log(userid, name, type, tdate) values('errol', 'ERROL FARRO', 'Login', '2/28/2011 11:32:00 AM')

What am I doing wrong?

Thx,

Errol
0
Comment
Question by:Errol Farro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34998229
what is the data type for the field tdate?

you might consider using getdate() function instead of that concatenated stuff?!

http://www.experts-exchange.com/A_1499.html
0
 

Author Comment

by:Errol Farro
ID: 34998367
The field type is: datetime. I get the same error message with getdate(). See below;

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]String or binary data would be truncated.
 
The error occurred in C:\inetpub\wwwroot\AccOpSys\login.cfm: line 56

54 :                         <cfquery name="rs" datasource="#conn#">
55 :                         insert into log(userid, name, type, tdate)
56 :                         values('#trim(session.accopsys_status_usr_username)#', '#trim(session.accopsys_status_name)#', 'Login', GETDATE())
57 :                         </cfquery>
58 :                         </cfoutput>

VENDORERRORCODE         8152
SQLSTATE         22001
SQL          insert into log(userid, name, type, tdate) values('errol', 'ERROL FARRO', 'Login', GETDATE())
DATASOURCE         AccOpSys
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 1000 total points
ID: 34998479

Perhaps it's one of the other fields causing the error.  

If tdate is defined to be "dateTime" then getDate() is your best bet.

Check the varchar length on name and type to see if they have enough characters
Perhaps name is only set to varchar(10)?
0
 

Author Closing Comment

by:Errol Farro
ID: 34998735
you were right, I had to trim the name field, that was causing the problem. getdate() works great. Thanks a lot.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question