Convert date to week - CFBuilder2018

Report · Feb 27, 2020

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>

<title>weekNbr</title>

<head>

</head>

function checkWeek() {

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

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

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

</script>

SELECT *

FROM APP.TBL_ORDER

WHERE ORDERID = 39

</cfquery>

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

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

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

</cfform>

</html>

Report · Feb 28, 2020

What you seek, is week().

HTH,

^ _ ^

Report · Feb 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 🙂

Report · Feb 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,

^ _ ^

Report · Feb 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>

Report · Mar 01, 2020

thks. This all together solve my problem. 🙂

Report · Mar 01, 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>

Report · Mar 01, 2020

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

Report · Mar 01, 2020

🙂

Convert date to week - CFBuilder2018

1 Correct answer