Avatar of mahpog
mahpog
 asked on

Sytnax for converting a string date into smalldatetime format using coldfusion

<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.
ColdFusion Language

Avatar of undefined
Last Comment
_agx_

8/22/2022 - Mon
gdemaria

> <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)#
mahpog

ASKER
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.
ASKER CERTIFIED SOLUTION
gdemaria

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
mahpog

ASKER
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>
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
mahpog

ASKER
Gave good information and responsive. thx!
_agx_

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>
_agx_

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 :).
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.