CF - Form insert date into SQL table

I am trying to get a date value to insert into a SQL table.  It has to be a date format in the table because it has to be used in a calculation.  

The mvpdate field in the SQL table is a date data type.  
The datatype on the form is CF_SQL_Date and the form value is
<cfinput type="hidden" name="mvpdate" value="#DateFormat(DateAdd('m', -1, MyDateTime),'YYYYMMDD')#">

I run the form and it gives me this error


August is an invalid date or time string..
<cfqueryparam value="#form.mvpdate#" cfsqltype="CF_SQL_Date"> ,

Is there something else I should us except YYYYMMDD?

Any help is appreciated.
JohnMac328Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plusone3055Commented:
Have you Tried
<cfinput type="hidden" name="mvpdate" value="#DateFormat(DateAdd('m', -1, MyDateTime),'YYYY-MM-DD')#">
0
gdemariaCommented:
I always just use this, works great:


    #createODBCdate(form.mvpdate)#


You don't have to worry about SQL injection here as the data function would fail if it were a string, if you want to test it / validate it first you can just do ...

 <cfif NOT isDate(form.mvpDate)>
      <cfset form.mvpDate = ""> <!--- wasn't a date ---->
      ... handle not-a-date issue ...
 </cfif>
0
gdemariaCommented:
since the hidden date field is hidden, why format it?    or format it to the default  date format for your region: such as mm/dd/yyyy
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

JohnMac328Author Commented:
Ok - it now does insert the record but I still can't get this query to bring up the record based on that field - I have dates in the table but this will not grab them

WHERE     (nominations.mvpdate = DATEADD(month, - 1, nominations.mvpdate))
ORDER BY nominations.recordid DESC
0
JohnMac328Author Commented:
Here is the date in the table

2013-08-04
0
gdemariaCommented:
this would not match any record..

WHERE     (nominations.mvpdate = DATEADD(month, - 1, nominations.mvpdate))

you are using the column nominations.mvpdate  on both sides, it is the same as saying

where X = X - 1

That will never happen
0
JohnMac328Author Commented:
I get a syntax error when I try to take it out.
0
_agx_Commented:
You need to replace it with the right comparison.

What records are you trying to retrieve?

* Records from the previous month
* Records for a specific date only ...?
* Records for a specific date range:   from x to y?
0
gdemariaCommented:
don't take it out, replace it with what you really needed there..

Can you say what the clause is supposed to be filtering for?

As an example, right now you are saying..   I need all the records whose mvpDate is exactly one month before the mvpDate of that record.

What should it be?   One month before the user's input date of form.mvpDate  ?
0
JohnMac328Author Commented:
Just the previous month - the form correctly inserts the previous month.
0
JohnMac328Author Commented:
This returns nothing

  (nominations.mvpdate = DATEADD(month, - 1, GETDATE()))
0
_agx_Commented:
> nominations.mvpdate = DATEADD(month, - 1, GETDATE())

That's only comparing the records to a single date - not the entire month.  Plus getDate() contains a time. So it's unlikely you'd find a match anyway.

To get all dates in the previous month, you need to use something like this:

<cfset firstDayOfThisMonth= parseDateTime(dateFormat(now(), "yyyy-mm")& "-01"))>
<cfset firstDayOfLastMonth = dateAdd("m", -1, firstDayOfThisMonth)>

WHERE   mvpdate  >=  <cfqueryparam value="#firstDayOfLastMonth#" ...>
AND       mvpdate < <cfqueryparam value="#firstDayOfThisMonth#" ...>

You could also use SQL date functions like below, but that's known to have a negative impact on index usages.

           WHERE   Month(dateColumn) = #val(someMonth)#
           AND        Year(dateColumn) = #val(someYear)#
0
JohnMac328Author Commented:
The date in the table is 8-4-2013 so this

 (nominations.mvpdate = DATEADD(month, - 1, GETDATE()))

is the month of August so it should bring it up - I could add another date field with the Now() as the default value but why will the above code not bring up the record?
0
_agx_Commented:
> is the month of August

Not sure what you mean by that.  

Finding a match depends on two things:

* What values are contained in mvpDate ie (date only) or (date and time)
* The value you're matching it against ie (date only) or (date and time)

Say it's 10:18AM right now.  Your expression is saying find all records with the exact value.

      WHERE  mvpDate = 'August 4, 2013 10:18 AM'

If you're not finding a match, that means none of the records have a date and time of 'August 4, 2013 10:18 AM'. Keep in mind your current expression is NOT the same as saying

* find all records dated anytime *within* August 2013  OR
* find records with a date (only) of 'August 4, 2013 12:00:00 AM'

For that you'll need a WHERE filter like the one I posted in my last response.
0
JohnMac328Author Commented:
Hi agx,

The value in the table is
2013-08-04

the data type for mvpdate in the sql table is date
I thought it would still see that it was August and ignore the day
0
_agx_Commented:
Nope.  Datetime values are always compared down to seconds or milliseconds. If you're looking for a range (like a whole month) you must use the comparison I mentioned earlier.

Also, even when you just use a date, there's an implicit time portion ie 12 midnight.  So when you compare the values, it's actually saying:

ie   WHERE   'August 4, 2013 00:00:00 AM'  =  'August 4, 2013 10:18 AM'

The db sees the times don't match. That's why it doesn't return anything:

EDIT

> the data type for mvpdate in the sql table is date

Date or DateTime?  If you mean SQL 2008+ "date" type, I haven't worked with it much, but I believe the same would still apply, due to implicit casting rules.
0
JohnMac328Author Commented:
Ok then I guess I will add a field with the default value of now() and do the compare that way.  Or maybe try just the month name
0
_agx_Commented:
I don't understand why you think you need to do that.

The query I posted above would return all records in August 2013, and would work for a single date too (just change the variables).  Did you try it?
0
JohnMac328Author Commented:
My bad - I scrolled and read too fast
0
_agx_Commented:
Ahh, ok.  I thought maybe we got our wires crossed ;-) since we covered both: ie what *would* work ... and why your current query *didn't* work.
0
JohnMac328Author Commented:
I can't see what is wrong with the syntax


Invalid CFML construct found on line 45 at column 78.
 
ColdFusion was looking at the following text:
)

The CFML compiler was processing:
•A cfset tag beginning on line 45, column 2.
 
 
 The error occurred in C:\Inetpub\wwwroot\Voting\vote.cfm: line 45
 
43 : <cfparam name="form.Team15" default=0>
44 : <cfparam name="form.astreason" default=0>
45 : <cfset firstDayOfThisMonth = parseDateTime(dateFormat(now(),"yyyy-mm")&"-01"))>
0
_agx_Commented:
EDIT: Ooops, my bad - typo. One too many close parenthesis.  

This should return all records dated anytime in August 2013:

<cfset firstDayOfThisMonth= parseDateTime(dateFormat(now(), "yyyy-mm")& "-01")>
<cfset firstDayOfLastMonth = dateAdd("m", -1, firstDayOfThisMonth)>

<cfquery ....>
SELECT   ...
FROM     ...
<!--- use cfsqltype="cf_sql_date" --->
WHERE   mvpdate  >=  <cfqueryparam value="#firstDayOfLastMonth#" ...>
AND       mvpdate < <cfqueryparam value="#firstDayOfThisMonth#" ...>
</cfquery>

This would return all records dated August 4, 2013 only:

<cfquery ....>
SELECT ..
FROM  ...
<!--- use cfsqltype="cf_sql_date" --->
WHERE   mvpdate  >=  <cfqueryparam value="#form.mvpdate#" ...>
AND       mvpdate < <cfqueryparam value="#dateAdd('d', 14, form.mvpdate)#" ...>
</cfquery>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnMac328Author Commented:
I need any day from August 2013 and the cfset is where the page keeps bombing before it gets to the sql

<cfset firstDayOfThisMonth = parseDateTime(dateFormat(now(),"yyyy-mm")&"-01"))>
<cfset firstDayOfLastMonth = dateAdd("m", -1, firstDayOfThisMonth)>
0
_agx_Commented:
Our posts keep clashing. There was a typo. See my edit above.
0
JohnMac328Author Commented:
Ok got to here


Invalid token . found on line 72 at column 66.
WHERE   mvpdate  >=  <cfqueryparam value="#firstDayOfLastMonth#" ...>
0
_agx_Commented:
You missed the comments ;-)  :

           <!--- use cfsqltype="cf_sql_date" --->

You need to replace the "..." with the correct cfsqltype.

(Since I can't test this right now, I was trying to keep it short and sweet)
0
JohnMac328Author Commented:
That should do it - thanks again agx!
0
_agx_Commented:
You're welcome, but as this was a two part question (insert and select queries), I think it should at least be a split w/gdemaria as he answered the first part :)
0
JohnMac328Author Commented:
I thought of that after I hit the button - please let me know when it is reset
0
_agx_Commented:
The moderators can reopen it for you. Just use the Request Attention link beneath the original question.
0
JohnMac328Author Commented:
Done - waiting on them to reset
0
JohnMac328Author Commented:
Thanks Guys
0
_agx_Commented:
Thanks John!
0
JohnMac328Author Commented:
No problem - just hope you two are happy with EE - :)
0
gdemariaCommented:
agx, John, Thanks for the consideration.  Much appreciated
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.