Skip to main content
A
Ariel_1234
Inspiring
February 27, 2020
Answered

Convert date to week - CFBuilder2018

  • February 27, 2020
  • 3 replies
  • 1530 views

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 has been closed for replies.
Correct answer Ariel_1234

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


🙂

3 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
February 29, 2020

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>

 

 

A
Ariel_1234Author
Inspiring
March 1, 2020

thks. This all together solve my problem. 🙂

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    March 1, 2020

    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>

     

     

     

     

     

     

     

    WolfShade
    WolfShade
    Legend
    February 28, 2020

    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,

     

    ^ _ ^

    WolfShade
    WolfShade
    Legend
    February 28, 2020

    What you seek, is week().

     

    HTH,

     

    ^ _ ^

    A
    Ariel_1234Author
    Inspiring
    February 28, 2020

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

    Newbie 🙂

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