First Day of the month

[NeoInJS]

Hi - how can I set the my Date field in a Submission to have a default value set to the first day of the current month?

[MayMusic]

You can change the form element of your field to be a Calculated Value on your submission page and use the formula below:

 

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

 

[Aether]

Hello @NeoInJS,

For you to be able to get the first day of the month, you should use "calculated value" as the form element then paste this formula:
 

CONVERT(DATETIME, (CAST(Month(SysUTCDateTime()) AS VARCHAR) + "/01/" + CAST(Year(SysUTCDateTime()) AS VARCHAR)), 101)

I hope this helps.

~WatashiwaJin~

[NeoInJS]

Thank @WatashiwaJin. However, I would still like my users to be able to change the field. I want to see that field populated with the First day of the month when the DataPage loads.

[DefinitelyNot31337]

Hi @NeoInJS,

 

Having the date field default to first day of the month, and have the field be editable at the same time is currently not possible in Submission Forms without JavaScript customization.

 

Too keep things simple in cases of "editable-default" value , I usually just setup a Virtual Field that is a placeholder of the custom user-input value, then have a Calculated Value for the actual field to be either the "user-input value", or the default value if it is blank.

 

You may do so by following these instructions:

1.) Create a Virtual Field that will hold the user-input values. (I would suggest creating a not for your users that say "Defaults to first day of the month").

2.) For the actual date/time field, you may set the Form Element to a "Calculated Value", then use the syntax below (For this example, I assumed that you use Virtual 1 as your placeholder):

CASE WHEN DATALENGTH('[@cbParamVirtual1]') > 0

THEN
TRY_CONVERT(date, '[@cbParamVirtual1]')

ELSE
DateAdd(
  day,
  1 - DatePart(day, '[@cbTimestamp*]'),
  '[@cbTimestamp*]'
)

END

 

Hope this helps.

 

-DN31337

 

[Aether]

17 hours ago, NeoInJS said:

Thank @WatashiwaJin. However, I would still like my users to be able to change the field. I want to see that field populated with the First day of the month when the DataPage loads.

Hi @NeoInJS -- if that is the case then I suggest that you follow these steps:

- Add a "Virtual field" that uses "Calculated value" as the form element and paste the formula:
 

CONVERT(DATETIME, (CAST(Month(SysUTCDateTime()) AS VARCHAR) + "/01/" + CAST(Year(SysUTCDateTime()) AS VARCHAR)), 101)

- You can also hide the Virtual field -> go to the advance option then check "Hide Field:

- Now, we are going to create a script that will get the value form the formula. For us to do that we need to add a "Header and Footer" -> then in the footer paste this code:
 

<script type="text/javascript">
document.addEventListener('DataPageReady', function (event) {
 
  var vrt = document.querySelector('[name*=cbParamVirtual1]');
  var date = document.querySelector('[name*=InsertRecordYOURDATEFIELD]');
 
  vrt.onchange = function() {
  date.value = this.value.substring(0,9);
  }
  });
</script>

- Make sure that you unchecked the "Enable HTML Editor" in the advance tab of the Footer.

- After this process, your date field will get the first day of the month and it still can be editable 

 

For more details, check these article:

https://howto.caspio.com/function-reference/

https://howto.caspio.com/datapages/ajax-loading/

I hope this helps  

~WatashiwaJin~

[GoodBoy]

Hi there! If ever you would like to also get the last day of the month, you may use this formula.

DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, SysDateTime()) + 1, 0))

[Merikirin]

Hello! If you want to add a formula field on the table using that workflow. You may check this formula:

The formula for the first day of the month:
DATEADD(MONTH, DATEDIFF(MONTH,-1, [@field:Date]) -1,0)

The formula for the last day of the month:
DATEADD(MONTH, DATEDIFF(MONTH,-1, [@field:Date]) ,-1)

For the [@field:Date] you can use either Date/Time or Timestamp for the field's data type on the formula.

[Tubby]

On 9/4/2023 at 3:16 PM, Merikirin said:

Hello! If you want to add a formula field on the table using that workflow. You may check this formula:

The formula for the first day of the month:
DATEADD(MONTH, DATEDIFF(MONTH,-1, [@field:Date]) -1,0)

The formula for the last day of the month:
DATEADD(MONTH, DATEDIFF(MONTH,-1, [@field:Date]) ,-1)

For the [@field:Date] you can use either Date/Time or Timestamp for the field's data type on the formula.

Better yet,  you can just use 0 for the first day of the month. While the formula works, it kind of confused me how it worked and where the need to subtract 1 came from.
First day of the month formula:
DATEADD(MONTH, DATEDIFF(MONTH,0, [@field:Date]),0)

Then for the last day of the month:

DATEADD(MONTH, DATEDIFF(MONTH,0, [@field:Date)+1 ,-1)

Here is how it works:

For the first day of the month:

DATEDIFF(MONTH,0, [@field:Date]) calculates the difference between 0 and your date field in months. 0 is a reference of the earliest date possible in SQL. For example, January 1, 1900. No matter what the earliest date is, it gets the difference in months and returns it as an integer. In the example, it will return 1639 if we select any date inside September 2023.

Now, it leaves us with this: DATEADD(MONTH, 1639,0)

What it does is to add 1639 months to 0, which from my explanation above, is the reference date or January 1, 1900. Since it is just adding months to the reference date, it stays on the day of the month where it currently is, which is the 1st day of January. Once 1639 months is added, it becomes September 1, 2023, which is the first day of the month for the selected date.

As for the last day of the month, its the same process except we are adding 1 month to the result of the DATEDIFF(MONTH,0, [@field:Date]). So in the same example, we get 1640 instead of 1639.

Then it leaves us DATEADD(MONTH, 1640, -1). -1 in this formula offsets 1 day from the reference date. So if 0 is January 1, 1900, it gets December 31, 1899, then it adds 1640 months to it which results to the last day of September or whatever month it lands on.

I hope this helps anyone who wants to customize this code and is trying to understand how it works! Cheers!