Solved

Add Date/time field to SQL database

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now