Solved

Add Date/time field to SQL database

Posted on 2011-02-28
4
594 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 142

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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 …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

786 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