Skip to main content
Ariel_1234
Inspiring
Feb 27, 2020 9:43 PM UTC
Answered

Convert date to week - CFBuilder2018

My application is storing orderdata, including ‘DateOfDelivery’ - DoD, in a database (local).

I wish to use DoD  to calculate the corresponding weeknumber and found a method (se printout).

When the function is called from onClick getElementById id=”week” is updated with correct weeknumber -  but the result is only displayed.  I want to capture the value and save it in the database. Is it possible in CF?

Ariel_1234

 

______________

<!DOCTYPE html>

<html>

<meta charset="utf-8">

<meta name="viewport" content="width=device-width, initital-scale=1.0">

<title>weekNbr</title>

 <link href="home.css" rel="stylesheet" type="text/css">

<link href="createNewOrder.css" rel="stylesheet" type="text/css"> 

 

<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js"></script>

<head>

</head>     

<script>

function checkWeek() {

         var s = document.getElementById('test').value;

         var m = moment(s, 'YYYY-MM-DD');

               document.getElementById('Week').value = m.format('W');

        </script>

 

  <cfquery datasource="ajour" name="getOrderInfo">

       SELECT *

       FROM APP.TBL_ORDER

       WHERE ORDERID = 39

</cfquery>

<div class="display" >    

             <TABLE id="displayOrderLine" CELLPADDING="3" CELLSPACING="0">

                    <tr>

                           <th>Order ID</th>

                           <th>Supplier</th>

                           <th>Season </th>

                           <th>Date of delivery</th>

                           <th>WeekNbr</th>

                           <th>Total</th>

                           <th>Status</th>                                      

                    </tr>

                    <CFOUTPUT QUERY="getOrderInfo">

                           <TR BGCOLOR="##C0C0C0">

                               <TD>#ORDERID#</TD>

                                <TD>#SPLRNAME#</TD>

                                <TD>#SEASON#</TD>

                               <TD>#DATEOFDELIVERY#</TD>

                               <TD>#WEEKNBR#</TD>

                               <TD>#GRANDTOTAL#</TD>

                               <TD>#STATUS#</TD>

                           </TR>   

                    </CFOUTPUT>

             </TABLE>

</div>

<cfset deliveryDate = #getOrderInfo.DATEOFDELIVERY#/>

<cfform id="testing">

       <cfinput name="dString" id="test" value="#deliveryDate#">

       <button onclick="checkWeek(this)">Check week number</button><br>

</cfform>

<input id="Week"><br>

</html>

 

 

This topic is closed to new replies. Start a new post to keep the conversation going.
Ariel_1234Correct answer
Inspiring
Mar 1, 2020 4:20 PM UTC

Sorry about the corrections. I'm editing on a tiny screen and saving versions so I don't lose them. 


🙂

View reply

8 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
Feb 29, 2020 1:52 PM UTC

To add to WolfShade's suggestion, here's another, which: 

1) requires no Javascript;

2) defines the argument of week() as a datetime object

 

 

 

<!DOCTYPE html>
<html>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initital-scale=1.0">
<title>weekNbr</title>
 <link href="home.css" rel="stylesheet" type="text/css">
<link href="createNewOrder.css" rel="stylesheet" type="text/css"> 
 
<head>
</head>     
<cfquery datasource="ajour" name="getOrderInfo">
       SELECT *
       FROM APP.TBL_ORDER
       WHERE ORDERID = 39
</cfquery>
<div class="display" >    
             <TABLE id="displayOrderLine" CELLPADDING="3" CELLSPACING="0">
                    <tr>
                           <th>Order ID</th>
                           <th>Supplier</th>
                           <th>Season </th>
                           <th>Date of delivery</th>
                           <th>WeekNbr</th>
                           <th>Total</th>
                           <th>Status</th>                                      
                    </tr>
                    <CFOUTPUT QUERY="getOrderInfo">
                           <TR BGCOLOR="##C0C0C0">
                               <TD>#ORDERID#</TD>
                                <TD>#SPLRNAME#</TD>
                                <TD>#SEASON#</TD>
                               <TD>#DATEOFDELIVERY#</TD>
                               <TD>#WEEKNBR#</TD>
                               <TD>#GRANDTOTAL#</TD>
                               <TD>#STATUS#</TD>
                           </TR>   
                    </CFOUTPUT>
             </TABLE>
</div>

<cfif isDate(getOrderInfo.dateOfDelivery)>
	<cfset deliveryDate = parseDatetime(getOrderInfo.dateOfDelivery)>
	<cfset weekNumber = week(deliveryDate)>

	<!--- Query to insert week-number to database --->

	<cfform id="testing">
	       <cfinput name="dString" id="test" value="#dateFormat(deliveryDate, 'YYYY-MM-DD')#">
		<p>Week number:
	       <cfinput name="WeekNumber" id="wknr" value="#weekNumber#">
		</p>
	</cfform>
<cfelse>
Error: getOrderInfo.dateOfDelivery is not a datetime object.
</cfif>

</html>

 

 

Translate
Ariel_1234Author
Inspiring
Mar 1, 2020 9:01 AM UTC

thks. This all together solve my problem. 🙂

Translate
    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    Mar 1, 2020 11:07 AM UTC

    When I reread this thread, I realize that we had been dealing with just one value of getOrderInfo.dateOfDelivery. And outside of the query, too. 

    To make everything dynamic, including inserting the week number into the database, you could do something like:

     

     

     

     

    <!DOCTYPE html>
<html>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initital-scale=1.0">
<title>weekNbr</title>
 <link href="home.css" rel="stylesheet" type="text/css">
<link href="createNewOrder.css" rel="stylesheet" type="text/css"> 
 
<head>
</head>     
<cfquery datasource="ajour" name="getOrderInfo">
       SELECT *
       FROM APP.TBL_ORDER
       WHERE ORDERID = 39
</cfquery>
<div class="display" >    
             <TABLE id="displayOrderLine" CELLPADDING="3" CELLSPACING="0">
                    <tr>
                           <th>Order ID</th>
                           <th>Supplier</th>
                           <th>Season </th>
                           <th>Date of delivery</th>
                           <th>WeekNbr</th>
                           <th>Total</th>
                           <th>Status</th>                                      
                    </tr>
                    <CFOUTPUT QUERY="getOrderInfo">
                           <TR BGCOLOR="##C0C0C0">
                               <TD>#ORDERID#</TD>
                                <TD>#SPLRNAME#</TD>
                                <TD>#SEASON#</TD>
                               <TD>#DATEOFDELIVERY#</TD>
				<cfif isDate(getOrderInfo.dateOfDelivery)>
					<cfset deliveryDate = parseDatetime(getOrderInfo.dateOfDelivery)>
					<cfset weekNumber = week(deliveryDate)>
					<cfquery datasource="ajour">
						UPDATE APP.TBL_ORDER 
						SET WeekNbr = #weekNumber#
						WHERE OrderID = #getOrderInfo.OrderID#
					</cfquery>
					<TD>#weekNumber#</TD>
				<cfelse>
				<TD>Error: '#getOrderInfo.dateOfDelivery#' not datetime object</TD>
				</cfif>
                               <TD>#GRANDTOTAL#</TD>
                               <TD>#STATUS#</TD>
                           </TR>   
                    </CFOUTPUT>
             </TABLE>
</div>

</html>

     

     

     

     

     

     

     

    Translate
    WolfShade
    WolfShade
    Legend
    Feb 28, 2020 7:25 PM UTC

    Professionally, I avoid everything W3 Schools.  Unreliable, although I have heard that they got better.

     

    If you are looking for a client-side solution, you'd probably have to code that yourself.. but, then again, maybe someone already made a function for that.  But you can do it server-side in CF with week().

     

    V/r,

     

    ^ _ ^

    Translate
    WolfShade
    WolfShade
    Legend
    Feb 28, 2020 5:39 PM UTC

    What you seek, is week().

     

    HTH,

     

    ^ _ ^

    Translate
    Ariel_1234Author
    Inspiring
    Feb 28, 2020 6:48 PM UTC

    thks. I was somewhat misled by https://www.w3schools.com/tags/att_input_type_week.asp - not supported by all browsers (FF).

    Newbie 🙂

    Translate
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices