Solved

Add Date/time field to SQL database

Posted on 2011-02-28
4
595 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
  • 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 250 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

792 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