DateTime keeps decreasing hour by 1 when updating Azure MS SQL

Like (0) Share

Report

Posted on 15 Jul 2022 18:32:38 by tedqn77

127

Canvas app accessing Azure MS SQL database.

The edit fields consist of a DatePicker, Hour dropdown (00-12), Minute dropdown (00-59), AmPm (AM/PM)

If the hour is 0 or 1 AM, it sets correctly. But going up (2 - 11) it starts decreasing the hour by 1 when updating. So if I pick 2AM, it sets the hour as 1AM.

Code:

Patch(MealAttendance,
{id:ThisItem.id},
{
checkIn:DatePicker2.SelectedDate + Time(If(AmPmValue2.Selected.Value = "PM", 12, 0) + Mod(Value(HourValue2.Selected.Value), 12), Value(MinuteValue2.Selected.Value), 0)
,
vendor: DropdownVendor.SelectedText.Value})

Workaround using DateTimeValue

If I hard code the value using DateTimeValue("11/3/2019 12:11 PM") it sets correctly. But if I try to do string concatination it errors out.

Patch(MealAttendance,
{id:ThisItem.id},
{
checkIn:DateTimeValue(Text(DatePicker2.SelectedDate, DateTimeFormat.ShortDate) & " " & HourValue2.Selected.Value & " " & MinuteValue2.Selected.Value & " " & AmPmValue2.Selected.Value),
vendor: DropdownVendor.SelectedText.Value})

Categories:

General Questions

All responses (2)

Answers (0)

tedqn77 127 on 19 Jul 2022 at 20:41:16

Like (0)

Report

Re: DateTime keeps decreasing hour by 1 when updating Azure MS SQL

Thank you. The issue seems to go away after the refresh. It's strange.
TheRobRush 11,121 Moderator on 15 Jul 2022 at 21:10:04

Like (1)

Report
Re: DateTime keeps decreasing hour by 1 when updating Azure MS SQL

Not a reply to your exact format you have going on, but I literally just finished setting up a time changer myself so figured I would share with you.

Mine has a HR text input, and a MINUTE text input and a toggle next to them thats false TEXT is set to AM and true text set to AM (can just drop this part if making users type in military time

The label that opens my time changer has onselect of

Set(whattimeisit,Now()); Set(changetimeVar,true);

this opens my small gallery that is my date changer, It's items are[1] and gallery template set to self.height

the default of my HR text input is

If( Hour(whattimeisit)<10, Concatenate("0",Hour(whattimeisit)), Hour(whattimeisit)>12, Hour(whattimeisit)-12, Hour(whattimeisit))

Minute text input default is

If( Minute(whattimeisit)<10, Concatenate("0",Minute(whattimeisit)), Minute(whattimeisit))

AM/PM toggle default is

If(And(Hour(whattimeisit)>11,Hour(whattimeisit)<24),true,false)

DatePicker Default is Today()

and my icon that saves all this into a datetimeformat that i then transfer later into sharepoint is

Set(whattimeisit, DateTimeValue( Concatenate( Switch( Toggle1_4.Value, false, TextInput3_4.Text, Value(TextInput3_4.Text)+12), ":", TextInput4_4.Text, " ", DatePicker1_4.SelectedDate) ) ); Set(changetimeVar,false)