DateFormat() Error

[khue]

In one of my coldfusion page that receives a charge number and updates the database, the codes looks like this:

<CFSET Expiration = '09/31/2003'>

<CFQUERY NAME=&quot;qryInsPayment&quot; DBNAME=&quot;#Application.DSN3#&quot;
DBTYPE=&quot;ODBC&quot; DATASOURCE=&quot;#Application.DSN3#&quot;>
INSERT INTO dbo.PaymentMethod
(Charge_Number,
Expiration_Date,
PaymentID)
VALUES ('#ChargeNo#',
'#DateFormat(Expiration,&quot;mm/dd/yyyy&quot#',
'1')&quot;
</CFQUERY>

and I don't know what's wrong with the date but it keeps giving me this error:

An error occurred while evaluating the expression:

#DateFormat(Expiration,&quot;mm/dd/yyyy&quot#

Error near line 208, column 17.

Parameter 1 of function DateFormat which is now &quot;09/31/2003&quot; must be a date/time value

ODBC Error Code = 22008 (Datetime field overflow)

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

SQL = &quot;INSERT INTO dbo.PaymentMethod ( Charge_Number, Expiration_Date, PaymentID ) VALUES ( '6117 3753 2881 4352', '09/31/2003'), '1' )&quot;

The error occurred while processing an element with a general identifier of (#DateFormat(Expiration,&quot;mm/dd/yyyy&quot#), occupying document position (208:16) to (208:52) in the template file c:\inetpub\

http://wwwroot\FamilyCenter\Payment\FC05007.cfm.

The date look right to me. Can someone help?

 

[tleish]

September 31, 2003 is an invalid date, try
<CFSET Expiration = &quot;09/30/2003&quot;>

Remember...

Thirty days hath September,
April, June, and November;

February has twenty-eight alone,
All the rest have thirty-one,

Excepting leap-year, that's the time
When February's days are twenty-nine. - tleish

 

[Silvano]

1) remove the single quotes arround the field that represent date value:

VALUES ('#ChargeNo#',
#DateFormat(Expiration,&quot;mm/dd/yyyy&quot#,...

2) make sure that Expiration_Date field in the database is Date/Time type;

3) consider use of CreateDate() function Sylvano
dsylvano@hotmail.com

 

[Guest_imported]

Turn on debugging and configure it so that you can see the SQL that is generated by your code. Then you can see exactly what is wrong with it. You probably need to use CreateODBCDateTime(&quot;mm/dd/yyyy&quot. Also, in SQL, dates are NOT enclosed in quotes.