Inspiring

Answered

CFSET DATEFORMAT

Forum|Forum|13 years ago
May 23, 2013
2 replies
1318 views

I want to pass my date parameters to my stored procedure.

Because MS SQL uses format like 'YYYY-DD-MM', I want to convert cf_SQL_Date to the reight format to pass CFQUERY. I need include single quote.

I have following code, it seems that it does not work.

Your help and information is great appreciated,

</CFIF>

</CFIF>

execute MySP #form.StartDate#, #form.EndDate#

</cfquery>

Regards,

Iccsi,

This topic has been closed for replies.

Correct answer duncancumming

Any ColdFusion query object can be passed to <cfreport> (quote from Ben Forta's "Macromedia ColdFusion MX7 Certified Developer Study Guide"). So assuming your stored proc returns a query, it doesn't matter if you use <cfquery> or <cfstoredproc> to call it, in both cases you should get a query back. And regardles of how, that query can be passed. I'd expect something like this to work:

</cfstoredproc>

(I never use cfreport myself, but the online documentation wraps the name in # # instead of just passing the name as a string, so I assume that's correct).

A

Adam Cameron.

Inspiring

Because MS SQL uses format like 'YYYY-DD-MM', I want to convert cf_SQL_Date to the reight format to pass CFQUERY. I need include single quote.

This is incorrect. You SQL Server client might display them to you in that format, but that has no bearing on what you need to pass the server when it's expecting a date.

Just use your date value, and pass it using <cfqueryparam> and an appropriate CF_SQL_[type], depending on what sort of date value it needs to be.

Don't pass strings in place of dates to a DB if it's expecting a date.

And don't hard-code values in your SQL statements.

--

Adam

D

duncancumming

Participating Frequently

Some feedback:

1. It's recommended to use structKeyExists instead of IsDefined:

2. You shouldn't need the # # in most CFML tags:

3. Rather than use <cfquery> to execute a stored proc, I'd say it's preferable to use <cfstoredproc> :

</cfstoredproc>

4. You have a typo here:

Should be EndDate not EndtDate. So right now it's probably amending your EndDate to be Now() every time.

I'm unclear if your problem is with what's been submitted from your form, or with the default values you're creating.

I

iccsiAuthor

Inspiring

Thanks for the information and help,

I use this cfquery for cfreport.

cfstoredproc does not have name property that I can not refer in cfreport.

Thanks again for helping,

Regards,

Iccsi,

D

duncancummingCorrect answer

Participating Frequently

Any ColdFusion query object can be passed to <cfreport> (quote from Ben Forta's "Macromedia ColdFusion MX7 Certified Developer Study Guide"). So assuming your stored proc returns a query, it doesn't matter if you use <cfquery> or <cfstoredproc> to call it, in both cases you should get a query back. And regardles of how, that query can be passed. I'd expect something like this to work:

</cfstoredproc>

(I never use cfreport myself, but the online documentation wraps the name in # # instead of just passing the name as a string, so I assume that's correct).

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.