Solved

Add Date/time field to SQL database

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

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