Patching Blank Value for Date/Time Column in MS Lists

[as733y]

I was hoping someone could shed some light on why my patch code is still patching the default value (todays date) for a date/time column (Start Date and End Date).

I'm trying to have the Start Date and End Date column in my MS List be blank with the Request Date/Time column populated with the info from the date card.

I was successful with the 2nd Patch code below in populating the data card value to the Start Date and End Date column and leaving the Request Date/Time column Blank, so what am I missing in the code for the first Patch? 

The Request Date/Time does have Time included and the Start Date and End Date columns are dates only.

Patch(
    'Driver Scheduling',
    Defaults('Driver Scheduling'),
    {
        'Start Date': Blank(),
        'End Date': Blank(),
        Notes: DataCardValue10.Text,
        'Request Type': {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", 
            Value: DataCardValue9.Selected.Value
        },
        'Vacation Status': {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", 
            Value: Blank()
        },
        'Request Date/Time': DateValue1.SelectedDate + Time(
            Value(HourValue1.Selected.Value),
            Value(MinuteValue1.Selected.Value),
            0
        ),
        'Driver Assigned': DataCardValue13.SelectedItems
    }
);

Patch(
    'Driver Scheduling',
    Defaults('Driver Scheduling'),
    {
        'Start Date': DataCardValue11.SelectedDate,
        'End Date': DataCardValue12.SelectedDate,
        Notes: DataCardValue1.Text,
        'Request Type': {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
            ID: 0,
            Value: "Vacation"
        },
        'Vacation Status': {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
            ID: 0,
            Value: "Pending Approval"
        },
        'Request Date/Time': Blank()
    }
);

[RandyHayes]

@as733y 

Make sure you have the formula level error management feature turned on in your app.  Otherwise it will not write blank values.

Also, you can eliminate the @odata.type lines from your formulas - they haven't been needed now for over 2 years.

I hope this is helpful for you.

[as733y]

Thanks @RandyHayes 

Do you know, if I have a date/time column in Sharepoint Lists, if I'm able to just patch only date and ignore the time, even though the column is formated for date & time? I guess the formula level error management is experimental, so Blank() doesn't work?

[RandyHayes]

@as733y 

That feature has been "experimented" for over 4 years now.  It is pretty much a feature at this point.  And it is the only way to write a blank value to a column in your datasource.  What didn't work??

As for the Date and Time.  The choice of Date Only or Date Time in the column definition is more interface based than it is data based.  They are all DateTime columns.  The only difference with a Date Only column is that it is a Date with a time of "00:00:00" or 12 midnight.  So, if you want to patch just the date, then you can.  If you include the time, you can do that as well.  It will all be a DateTime value that you are using.  If you "technically" want to ignore the time, then set the time to 12 midnight as well. 

[as733y]

@RandyHayes 

Ok. Thank you for the explanation. 

Below is the Patch code that I'm using and is looks like 00:00 Z (7pm CT) is being Patched instead of Blank times. I tried to do a Start and End date of 7/21.

Patch(
    'Driver Scheduling',
    Defaults('Driver Scheduling'),
    {
        'Start Date': DataCardValue11.SelectedDate + Time(
            Blank(),
            Blank(),
            0
        ),
        'End Date': DataCardValue12.SelectedDate,
        Notes: DataCardValue1.Text,
        'Request Type': {Value: "Vacation"},
        'Vacation Status': {Value: "Pending Approval"}
    }
)

[RandyHayes · Accepted solution]

@as733y 

Yes, but you are looking at the "formatted" display of the SharePoint list.  When you view datetime values in a list, SharePoint uses the Regional Settings for the site to format and correct the time zone.  

You're in Central Time and it is Daylight Savings - That means that you are in UTC -5 time zone.

So, you will see that your date of (presumed today) 7/21/2022 00:00:00 has been adjusted to display in your time zone.  And as such, for you, that would be 5 hours earlier...which would be 7/20/2022 7:00 PM

[as733y]

Hi @RandyHayes

So I've been trying to find another solution for my app and am stuck on the below patch. The Start Date Patches correctly like below, however my End Date doesn't patch 23:59 like below? It's patching 7/26/2022 00Z (or 7/25/2022 1900) when it should be 7/26/2022 23:59? 

Patch(
    'Driver Scheduling',
    Defaults('Driver Scheduling'),
    {
        'Start Date': DataCardValue11.SelectedDate + Time(1,0,0
        ),
        'End Date': DataCardValue12.SelectedDate + Time(23,59,0),
        Notes: DataCardValue1.Text,
        'Request Type': {Value: "Vacation"},
        'Vacation Status': {Value: "Pending Approval"}
    }
)

[as733y]

@RandyHayes 

Nevermind. I ended up recreating the End Date column and it works. Not sure what was happening before.