Troubleshooting SQL Server date insert defaults to 1900-01-01 from Power Automate

Question

I am currently working on automating a process where clinicians' end dates for their clinics are updated in our SQL Server database. The data flows from an MS Form to SQL Server via Power Automate.

While the system works well for primary clinics, I'm facing a recurring issue where alternate clinics' end dates are being inserted as 1900-01-01, the default SQL date, rather than the actual dates selected in the form.

Here's the workflow:

1. MS Forms: Clinicians submit their end dates for various clinics through a form.
2. Power Automate: captures these submissions and passes the data to SQL Server.
3. SQL Server: a stored procedure called UpdatePhysicianClinicInfo updates the clinicians' information, including the end dates for their primary and alternate clinics.

The challenge:

• The primary clinic's end date updates perfectly.
• Alternate clinics' end dates revert to 1900-01-01 regardless of the input and attempts to format it in a date time format from Power Automate.

Here are the details:

• Environment: SQL Server, accessed via Azure Data Studio
• Scenario: The stored procedure, UpdatePhysicianClinicInfo, is intended to update clinic information based on data passed from an MS Form through Power Automate. Primary clinics update fine, but alternates result in the default 1900-01-01 date.

Attempts to resolve:

• Directly inserting the date from a SQL update script
• Formatting the date in Power Automate
• Various checks and balances within the stored procedure

Stored procedure code:

WHILE @i <= 3
BEGIN
    SET @EndDate = CASE
                       WHEN @i = 0 THEN @EndDateForCurrentPrimaryClinic
                       WHEN @i = 1 THEN @EndDateForCurrentAlternateClinic1
                       WHEN @i = 2 THEN @EndDateForCurrentAlternateClinic2
                       WHEN @i = 3 THEN @EndDateForCurrentAlternateClinic3
                   END;

    SET @ClinicName = CASE
                          WHEN @i = 0 THEN @CurrentPrimaryClinicName
                          WHEN @i = 1 THEN @CurrentAlternateClinicName1
                          WHEN @i = 2 THEN @CurrentAlternateClinicName2
                          WHEN @i = 3 THEN @CurrentAlternateClinicName3
                      END;

    SET @PortionOfPractice = CASE 
                                 WHEN @i = 0 THEN 'Primary'
                                 ELSE 'Alternate'
                             END;

    IF @ClinicName IS NOT NULL
    BEGIN
        SELECT @ClinicID = clinic_id 
        FROM mh_clinics 
        WHERE clinic_name = @ClinicName;
    END

    IF @EndDate IS NOT NULL
    BEGIN
        UPDATE mh_clinics_physicians 
        SET date_left_clinic = @EndDate,
            date_modified = GETDATE(),
            modified_by = @ModifiedBy
        WHERE physician_id = @PhysicianID 
          AND clinic_id = @ClinicID 
          AND portion_of_practice = @PortionOfPractice;
    END

    SET @i = @i + 1;
END

mh_clinics_physicians table (example):

clinics_physicians_ID	physician_id	physician_name	clinic_id	clinic_name	portion_of_practice	date_active_in_clinic	date_left_clinic
304	254	John Doe	3	Clinic A	Primary	2023-11-03	2023-11-18
305	254	John Doe	4	Clinic B	Alternate	2023-11-03	1900-01-01
306	254	John Doe	5	Clinic C	Alternate	2023-11-20	1900-01-01

Observations:

• No issues with primary clinic dates.
• Manual execution with sample data works as expected, but automated flow does not.
• The issue persists regardless of date formatting in Power Automate.

I'm perplexed as to why the default date is being inserted instead of the one provided. I've tried numerous workarounds with no success. If you've encountered a similar issue or have any insights into what might be going wrong, your expertise would be greatly appreciated!