[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 470
  • Last Modified:

Split Date into seperate variables for Month, Day Year

I need to pull a date from a MySQL field type DATE, and use it to populate three drop down menus, one for Month, one for Day, one for Year. Is there a way to do this?
0
ndcollins
Asked:
ndcollins
  • 7
  • 5
1 Solution
 
dgrafxCommented:
You could just do
#day(thisdate)# #month(thisdate)# #year(thisdate)#
where thisdate is the date pulled from query
0
 
ndcollinsAuthor Commented:
That doesn't seem to be doing anything. Here's what I've got

My Query (DATE field name is DateTrans)

<cfquery name="qTransEditGlobal" datasource="CDS_App_GivingBeta">
        SELECT *
        FROM GivingBeta.gifts_d
        WHERE UUID=#uuid#
      AND TransID=#transid#
</cfquery>
--------------------------------------------------------------------------------------------
The Date Fields

<cfselect name="Month" id="Month" required="yes" selected="#day(DateTrans)#">
    <option value="01">January</option>
    <option value="02">February</option>
    <option value="03">March</option>
    <option value="04">April</option>
    <option value="05">May</option>
    <option value="06">June</option>
    <option value="07">July</option>
    <option value="08">August</option>
    <option value="09">September</option>
    <option value="10">October</option>
    <option value="11">November</option>
    <option value="12">December</option>
      </cfselect>
      /
      <cfselect name="Day" id="Day" required="yes" selected="#month(DateTrans)#">
      <option value="01">1</option>
      <option value="02">2</option>
      <option value="03">3</option>
      <option value="04">4</option>
      <option value="05">5</option>
      <option value="06">6</option>
      <option value="07">7</option>
      <option value="08">8</option>
      <option value="09">9</option>
      <option value="10">10</option>
      <option value="11">11</option>
      <option value="12">12</option>
      <option value="13">13</option>
      <option value="14">14</option>
      <option value="15">15</option>
      <option value="16">16</option>
      <option value="17">17</option>
      <option value="18">18</option>
      <option value="19">19</option>
      <option value="20">20</option>
      <option value="21">21</option>
      <option value="22">22</option>
      <option value="23">23</option>
      <option value="24">24</option>
      <option value="25">25</option>
      <option value="26">26</option>
      <option value="27">27</option>
      <option value="28">28</option>
      <option value="29">29</option>
      <option value="30">30</option>
      <option value="31">31</option>
      </cfselect>
      /
      <cfselect name="Year" id="Year" required="yes" selected="#year(DateTrans)#">
      <option value="2004">2004</option>
      <option value="2005">2005</option>
      <option value="2006">2006</option></cfselect>
0
 
dgrafxCommented:
You have your day and month swithed around
0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
dgrafxCommented:
switched (sp) :)
0
 
ndcollinsAuthor Commented:
ok, thanks for pointing that out, but that doesn't change anything... it would just show up as April 1st instead of January 4th. I'm not getting any response at all
0
 
dgrafxCommented:
I assumed you had the hardcoded dates just for an example
I don't believe you can code that and expect cf to know exactly what you want.

Heres an example:
<select name="day">
<cfloop index="dd" from="1" to="#daysinmonth(qTransEditGlobal.DateTrans)#">
<option value="#dd#"<cfif dd is day(qTransEditGlobal.DateTrans)> selected</cfif>>#dd#
</cfoutput>
et cetera

But ...
This is actually a can of worms you're opening
Say the current month is march which has 31 days and you switch to Feb which has 28 (or 29) days - you need a script to rewrite the options in the day selectbox to the correct number.

Good luck!
0
 
ndcollinsAuthor Commented:
I've tried replacing the DateTrans with 2004-01-04

Still no response from the dropdown menus.

I have a date validation script in the form processor, so it will take care of invalid date entries.

What I'm doing is making a screen where you can update the information in the transaction, so I need to pull down what's currently in the database for the transaction, so they can see the current, edit it, and post it to the database. So in this case I need the dropdown's selected value to be the Month, Day, and Year from the DATE field of the databse. Make sense?
0
 
ndcollinsAuthor Commented:
Even if I hard code the selected value as, for example, "04" it isn't doing anything... weird
0
 
dgrafxCommented:
I don't know why you tried 2004-01-04 ???
Read my last post again or actually just use this:

<select name="day">
<cfloop index="dd" from="1" to="31">
<option value="#dd#"<cfif dd is day(qTransEditGlobal.DateTrans)> selected</cfif>>#dd#
</cfoutput>

<select name="month">
<cfloop index="dd" from="1" to="12">
<option value="#dd#"<cfif dd is month(qTransEditGlobal.DateTrans)> selected</cfif>>#dd#
</cfoutput>

<select name="year">
<cfloop index="dd" from="2000" to="#year(now())#">
<option value="#dd#"<cfif dd is year(qTransEditGlobal.DateTrans)> selected</cfif>>#dd#
</cfoutput>



0
 
dgrafxCommented:
I don't know why but I used a /cfoutput instead of /cfloop

<select name="day">
<cfloop index="dd" from="1" to="31">
<option value="#dd#"<cfif dd is day(qTransEditGlobal.DateTrans)> selected</cfif>>#dd#
</cfloop>

<select name="month">
<cfloop index="dd" from="1" to="12">
<option value="#dd#"<cfif dd is month(qTransEditGlobal.DateTrans)> selected</cfif>>#dd#
</cfloop>

<select name="year">
<cfloop index="dd" from="2000" to="#year(now())#">
<option value="#dd#"<cfif dd is year(qTransEditGlobal.DateTrans)> selected</cfif>>#dd#
</cfloop>


0
 
dgrafxCommented:
put the </select> after the </cfloop> also of course
0
 
ndcollinsAuthor Commented:
Cool, and I also added some CFIF statements to change the values to the proper name for each month, Thanks!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now