Sytnax for converting a string date into smalldatetime format using coldfusion

mahpog
mahpog used Ask the Experts™
on
<cfloop from="05/22/2012" to="05/24/2012" index="i_d" step="#CreateTimeSpan(1,0,0,0)#" > 

<cfoutput>#DateFormat(i_d,"mm-dd-yyyy")#<br /></cfoutput>

</cfloop>  
***********************
Result:
(i_d)
05-22-2012
05-23-2012
05-24-2012
**********************

- I need to convert i_d to date type output to store in a SQL base type of smalldatetime

I was going to use CreateDate but it wants the dates broken down into mm, dd, yyyy.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
> <cfloop from="05/22/2012" to="05/24/2012" index="i_d" step="#CreateTimeSpan(1,0,0,0)#" > 


wow, I didn't know that you could loop a date!  


I always use this for inserting..  never had a problem..

   #createOBDCdate(i_d)#

Author

Commented:
1. cut and pasted #createOBDCdate(i_d)#  it got an error.
error:
Variable CREATEOBDCDATE is undefined

code:
<cfloop from="05/22/2012" to="05/24/2012" index="i_d" step="#CreateTimeSpan(1,0,0,0)#" > 
<cfoutput>#createOBDCdate(i_d)#<br /></cfoutput>
</cfloop>  

********************************
<cfloop from="05/22/2012" to="05/24/2012" index="i_d" step="#CreateTimeSpan(1,0,0,0)#" > 

2. Change the first parameter in the CreatetimeSpan to 14 (days) for bi-weekly, 7(days) for weekly.
oops

createODBCdate()

... should be ODBC, as in ODBC.. sorry :)
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Author

Commented:
I changed as stated (thx!).

I changed my sql table basetype from smalldatetime to datetime because I got conversion error first time I ran.

Second run:
Sql Table Load:

id      date_started                             mtgtype                     time_min               repeat_type
7      12/31/1899 12:00:00 AM      Business-Fnctl            60                       1
7      1/1/1900 12:00:00 AM              Business-Fnctl            60                       1

*dates are complete wrong. Should be an entry for 05/22/2012. 05/23/2012, and 05/24,2012.

Actual Code:
******************************************************
<cfloop from="#FORM.date_started#" to="#FORM.date_ended#" index="i_d" step="#CreateTimeSpan(1,0,0,0)#">

<cfset dayout = "#ListAppend('dayout',createODBCdate(i_d))#">

</cfloop>          
                  
                              
<cfloop From = "1" To = "#ListLen(dayout)#" index = "i_d">
      <cfquery name="MetricInsert2" Datasource="#REQUEST.DSN.Source#">
                  INSERT INTO rcenter_mdata
                        (id, date_started, mtgtype, time_min, repeat_type)
                        VALUES (
      <cfqueryparam value="#qMAXID.ID#" cfsqltype="CF_SQL_INTEGER" />,
      <cfqueryparam value="#i_d#" cfsqltype="CF_SQL_TIMESTAMP"/>,                            
          <cfqueryparam value="#FORM.mtgtype#" cfsqltype="CF_SQL_CHAR" />,
          <cfqueryparam value="#dailymin#" cfsqltype="CF_SQL_INTEGER" />,
          <cfqueryparam value="#FORM.repeat_type#" cfsqltype="CF_SQL_INTEGER" />)
      </cfquery>
</cfloop>

Author

Commented:
Gave good information and responsive. thx!
Most Valuable Expert 2015

Commented:
I was going to use CreateDate but it wants the dates broken down into mm, dd, yyyy.

I don't know if you're even supposed to be able to use timespan that way...  IMHO, go with your instinct. Don't use unknown shortcuts with dates and times.  It's too easy to get wrong.  When you need dates - use dates (and date functions) :) That's what they're there for.  Not tested, but something like this:

<!--- note, assuming US dates ie mm/dd/yyyy --->
<cfset  loopDate = parseDateTime(FORM.date_started)>
<cfset  maxDate = parseDateTime(FORM.date_ended)>
<cfloop condition="#dateDiff('d', loopDate, maxDate)# lte 0">
         do something with the date, insert it  into a db etc....

          <cfquery ...>
           INSERT INTO Table (...)
           VALUES ( <cfqueryparam value="#loopDate#" cfsqltype="cf_sql_timestamp"> )
         </cfquery>

        <cfset loopDate = dateAdd("d", 1 , loopDate)>
</cfloop>
Most Valuable Expert 2015

Commented:
Ooops .. didn't see this had an accepted answer before responding. But I stand by my response. IMHO, don't use shortcuts to avoid using the proper tool: date functions :).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial