Hey Chris-
Try to CAST as a date. I had to do that for data that was entered in on a
project.
Syntax:
Using CAST:
CAST ( expression AS data_type)
Hope that helps.
Sona.
On 6/19/06 2:00 PM, "Ken Ferguson" <[EMAIL PROTECTED]> wrote:
> Well, it might not be the date, but what if you tried
> #dateFormat(ThisShiftDate, "dd-mmm-yy")#
> OR
> #dateFormat(ThisShiftDate, "dd-mmm-yyyy")#
>
> and see if it likes one of those any better?
>
> *****************
> Ken Ferguson
> 214.636.6126
> *****************
>
>
>
>
>
>
> Christopher Jordan wrote:
>> I hadn't. So I tried. Same result. My data type in the table is "date"
>> not timestamp, and I *want* it to be a date and not a timestamp. I'm
>> not so certain that it's the date anymore, even though that's where
>> the program halts. I changed the query to look like this:
>>
>> <CFQuery Name="AddNewNote" DataSource="#as400ds#">
>> INSERT INTO #PrependLibrary("Notes")#
>> (#ThisNoteInsertList#)
>> VALUES (
>> <CFQueryParam value="#ThisClientID#"
>> cfsqltype="CF_SQL_INTEGER">,
>> <CFQueryParam value="#ThisNoteID#"
>> cfsqltype="CF_SQL_INTEGER">,
>> <CFQueryParam value="#ThisNoteType#"
>> cfsqltype="CF_SQL_VARCHAR">,
>> <CFQueryParam value="#ThisNoteTimeStamp#"
>> cfsqltype="CF_SQL_TIMESTAMP">,
>> <CFQueryParam value="#ThisNoteText#"
>> cfsqltype="CF_SQL_VARCHAR">,
>> <CFQueryParam value="#ThisNoteAuthor#"
>> cfsqltype="CF_SQL_VARCHAR">,
>> <CFQueryParam value="#ThisNoteAuthorIPAddress#"
>> cfsqltype="CF_SQL_VARCHAR">
>> <CFIf Len(ThisEmployeeID)>
>> ,<CFQueryParam value="#ThisEmployeeID#"
>> cfsqltype="CF_SQL_INTEGER">
>> </CFIf>
>> <CFIf Len(ThisCostCenterID)>
>> ,<CFQueryParam value="#ThisCostCenterID#"
>> cfsqltype="CF_SQL_INTEGER">
>> </CFIf>
>> <CFIf Len(ThisShiftID)>
>> ,<CFQueryParam value="#ThisShiftID#"
>> cfsqltype="CF_SQL_INTEGER">
>> </CFIf>
>> <CFIf Len(ThisShiftDate)>
>> ,#ThisShiftDate#
>> <!---,<CFQueryParam value="#ThisShiftDate#"
>> cfsqltype="CF_SQL_DATE">--->
>> </CFIf>
>> )
>> </CFQuery>
>>
>> and I got the same results. I'm guessing my next steps will be to
>> remove all of the cfqueryparam statements, make sure my query runs
>> without them, and then start adding them back in one at a time. Or I
>> suppose I could remove them one at a time until the statement runs...
>> either way. ugh... :'(
>>
>> Thanks Ryan. Any other thoughts I'm open.
>>
>> Chris
>>
>> Ryan Everhart wrote:
>>> Chris,
>>> I am no DB2 guru, but have ou tried CF_SQL_TIMESTAMP as the SQL type?
>>>
>>> Ryan
>>>
>>>
>>> On 6/19/06, *Christopher Jordan* < [EMAIL PROTECTED]
>>> <mailto:[EMAIL PROTECTED]>> wrote:
>>>
>>> Hi folks,
>>>
>>> I'm getting a Data Truncation error with the following code snippet:
>>>
>>> <CFQuery Name="AddNewNote" DataSource="#as400ds#">
>>> INSERT INTO #PrependLibrary("Notes")#
>>> (#ThisNoteInsertList#)
>>> VALUES (
>>> <CFQueryParam value="#ThisClientID#"
>>> cfsqltype="CF_SQL_INTEGER">,
>>> <CFQueryParam value="#ThisNoteID#"
>>> cfsqltype="CF_SQL_INTEGER">,
>>> <CFQueryParam value="#ThisNoteType#"
>>> cfsqltype="CF_SQL_VARCHAR">,
>>> <CFQueryParam value="#ThisNoteTimeStamp#"
>>> cfsqltype="CF_SQL_TIMESTAMP">,
>>> <CFQueryParam value="#ThisNoteText#"
>>> cfsqltype="CF_SQL_VARCHAR">,
>>> <CFQueryParam value="#ThisNoteAuthor#"
>>> cfsqltype="CF_SQL_VARCHAR">,
>>> <CFQueryParam value="#ThisNoteAuthorIPAddress#"
>>> cfsqltype="CF_SQL_VARCHAR">
>>> <CFIf Len(ThisEmployeeID)>
>>> ,<CFQueryParam value="#ThisEmployeeID#"
>>> cfsqltype="CF_SQL_INTEGER">
>>> </CFIf>
>>> <CFIf Len(ThisCostCenterID)>
>>> ,<CFQueryParam value="#ThisCostCenterID#"
>>> cfsqltype="CF_SQL_INTEGER">
>>> </CFIf>
>>> <CFIf Len(ThisShiftID)>
>>> ,<CFQueryParam value="#ThisShiftID#"
>>> cfsqltype="CF_SQL_INTEGER">
>>> </CFIf>
>>> <CFIf Len(ThisShiftDate)>
>>> ,<CFQueryParam value="#ThisShiftDate#"
>>> cfsqltype="CF_SQL_DATE">
>>> </CFIf>
>>> )
>>> </CFQuery>
>>>
>>>
>>> The issue appears to be with the CF_SQL_DATE line. My DB2 table
>>> is set up such that the field called ShiftDate is of datatype
>>> "DATE", and the value of "ThisShiftDate" at the time I'm trying
>>> to insert it is: {d '2006-18-06'}
>>>
>>> I'm at a loss. I've only just begun to use CFQueryParam and I
>>> wonder if I'm doing something wrong or if it's just the stupid
>>> DB2 table or what?
>>>
>>> Any DB2 gurus in the group? I've got no idea what version of DB2
>>> I'm on, but I'd be willing to bet it's rather old, as the AS400
>>> it resides on is quite old. Help would be greatly appreciated. :)
>>>
>>>
>>> Thanks,
>>> Chris
>>>
>>> _______________________________________________
>>> Reply to DFWCFUG:
>>> [email protected] <mailto:[email protected]>
>>> Subscribe/Unsubscribe:
>>> http://lists1.safesecureweb.com/mailman/listinfo/list
>>> List Archives:
>>> http://www.mail-archive.com/list%40list.dfwcfug.org/
>>> http://www.mail-archive.com/list%40dfwcfug.org/
>>> DFWCFUG Sponsors:
>>> www.HostMySite.com <http://www.HostMySite.com>
>>> www.teksystems.com/ <http://www.teksystems.com/>
>>>
>>>
>>>
>>>
>>>
>>> --
>>> Ryan Everhart
>>> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
>>>
>>> _______________________________________________
>>> Reply to DFWCFUG:
>>> [email protected] <mailto:[email protected]>
>>> Subscribe/Unsubscribe:
>>> http://lists1.safesecureweb.com/mailman/listinfo/list
>>> List Archives:
>>> http://www.mail-archive.com/list%40list.dfwcfug.org/
>>> http://www.mail-archive.com/list%40dfwcfug.org/
>>> DFWCFUG Sponsors:
>>> www.HostMySite.com <http://www.HostMySite.com>
>>> www.teksystems.com/ <http://www.teksystems.com/>
>>>
>> ------------------------------------------------------------------------
>>
>> _______________________________________________
>> Reply to DFWCFUG:
>> [email protected]
>> Subscribe/Unsubscribe:
>> http://lists1.safesecureweb.com/mailman/listinfo/list
>> List Archives:
>> http://www.mail-archive.com/list%40list.dfwcfug.org/
>> http://www.mail-archive.com/list%40dfwcfug.org/
>> DFWCFUG Sponsors:
>> www.HostMySite.com
>> www.teksystems.com/
>>
>
>
> _______________________________________________
> Reply to DFWCFUG:
> [email protected]
> Subscribe/Unsubscribe:
> http://lists1.safesecureweb.com/mailman/listinfo/list
> List Archives:
> http://www.mail-archive.com/list%40list.dfwcfug.org/
> http://www.mail-archive.com/list%40dfwcfug.org/
> DFWCFUG Sponsors:
> www.HostMySite.com
> www.teksystems.com/
_______________________________________________
Reply to DFWCFUG:
[email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.HostMySite.com
www.teksystems.com/
