• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Convert date to week - CFBuilder2018

Community Beginner ,
Feb 27, 2020 Feb 27, 2020

Copy link to clipboard

Copied

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>

 

 

TOPICS
Builder , Getting started

Views

1.3K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Community Beginner , Mar 01, 2020 Mar 01, 2020

🙂

Votes

Translate

Translate
LEGEND ,
Feb 28, 2020 Feb 28, 2020

Copy link to clipboard

Copied

What you seek, is week().

 

HTH,

 

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Feb 28, 2020 Feb 28, 2020

Copy link to clipboard

Copied

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

Newbie 🙂

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 28, 2020 Feb 28, 2020

Copy link to clipboard

Copied

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,

 

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 29, 2020 Feb 29, 2020

Copy link to clipboard

Copied

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>

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Mar 01, 2020 Mar 01, 2020

Copy link to clipboard

Copied

thks. This all together solve my problem. 🙂

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 01, 2020 Mar 01, 2020

Copy link to clipboard

Copied

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>

 

 

 

 

 

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 01, 2020 Mar 01, 2020

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Mar 01, 2020 Mar 01, 2020

Copy link to clipboard

Copied

LATEST

🙂

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation