Questions about the SQL Get Rows Based on the date being greater than 01-01-2024 and if it is possible to have a SQL Query in the Select Query

[SPS-DEV-22]

I'm stuck, been trying to figure out two items

1) Filter Query

been trying to figure out a way to get the SQL Data from SQL that starts at the beginning of this year going forward.

here is the issue in SQL the Date column is Date: 19950724.000000000000000000

which I use SQL Convert 

CONVERT(DATE, CONVERT(VARCHAR(8), cast(IDDATEA as numeric(8,0))), 112) AS Date

to get a real date

1995-07-24

I don't think this matter:

in SharePoint list where the SQL data needs to go the Date column is this

1/1/2024

2) Select Query

Can I use this Query in the 

SELECT TOP (5)
LTRIM(RTRIM(REPLACE([IDITMN],' ',''))) AS IDITMN,
LTRIM(RTRIM(REPLACE([IDLNAM],' ',''))) AS IDLNAM,
LTRIM(RTRIM(REPLACE([IDFNAM],' ',''))) AS IDFNAM,
LTRIM(RTRIM(REPLACE([IDMINI] ,' ',''))) AS IDMINI,
LTRIM(RTRIM(REPLACE([IDDISTA] ,' ',''))) AS IDDISTA,
CONVERT(DATE, CONVERT(VARCHAR(8), cast(IDDATEA as numeric(8,0))), 112) AS Date
FROM [Data].[IMARRD]
WHERE [IDDATEA] >= 20240101 and [IDDATEA] <= 20241201

to be honest if the Select Query can be used to run the SQL Query then Question #1 is a mute point. but if I can not use the Query than I need to have assistance on Question #1.

Any answers to the Questions are greatly appreciated.

[abm]

Hi @SPS-DEV-22 

A while ago I have solved an issue related to date filter query in SQL.

Here is the thread and it might be useful to you.

Solved: Re: SQL GetRows OData Date Filter - Page 2 - Power Platform Community (microsoft.com)

Thanks

[SPS-DEV-22]

can not get to the link anymore:

www.svenpauwels.com/microsoft-flow/flow-sql-server-connector-how-to-filter-the-date

[abm]

Hi @SPS-DEV-22 

I got a screenshot of how I resolved it. Pls have a look.

Thanks

[SPS-DEV-22]

where is screen shot? on the forum page???

if I'm correct in Assuming 

when I use this:

IDDATEA ge date(2024-01-01T00:00:00Z)

I get this error 

Flow save failed with code 'InvalidVariableOperation' and message 'The inputs of workflow run action 'Get_rows_(V2)' of type 'OpenApiConnection' are not valid. The variable 'Date' must be initialized before it can be used inside action 'Get_rows_(V2)'.'.

[abm]

Hi @SPS-DEV-22 

Thanks for you quick reply. Please see below. Looks like you have the similar config. Could you please post a screenshot of your flow?

[abm]

Hi @SPS-DEV-22 

Are you trying to use any variables in your flow? A screenshot will be helpful to determine the issue.

Thanks

[SPS-DEV-22]

this did not work it actually made the flow error out:

Error Message:

No function signature for the function with name 'date' matches the specified arguments. The function signatures considered are: date(Edm.DateTimeOffset); date(Edm.DateTimeOffset Nullable=true).
inner exception: No function signature for the function with name 'date' matches the specified arguments. The function signatures considered are: date(Edm.DateTimeOffset); date(Edm.DateTimeOffset Nullable=true).
clientRequestId: 4edd6733-ffe8-4d0d-8814-8fe3380dd0b0

just an FYI in SQL server the IDDATEA Column has the date like this: 

19950724.000000000000000000

that is why when I run a query I 

CONVERT(DATE, CONVERT(VARCHAR(8), cast(IDDATEA as numeric(8,0))), 112) AS Date

or I need to figure out a way to get rid of the Zeros after the Decimal Point, and get the format different, with in the Power Automate flow

[abm]

Hi @SPS-DEV-22 

What the data type for column IDDATEA?

If you want to get rid of the decimals the use expression split()

split(string(19950724.000000000000000000),'.')

Then use the first() expression to get 19950724

first(split(string(19950724.000000000000000000),'.'))

This will give you 19950724

[SPS-DEV-22]

I did not set up the database but I inherited the issues, can't fix due to SLA.

how ever, it is (decimal 38, 18, null)

I have kind of solved that issue, but the decimal and the date/time format in SharePoint is causing an issue not when I try to push the data to SharePoint.

filter

IDDATEA ge '20240101.000000000000000000'

now trying to update the SharePoint list and i get this error based on that column

this is the new error:

Flow save failed with code 'OpenApiOperationParameterValidationFailed' and message 'Input parameter 'item' validation failed in workflow operation 'Update_item': The parameter with value '"@items('Apply_to_each_2')?['IDDATEA']"' in path 'item/field_6' with type/format 'Number/double' is not convertible to type/format 'String/date'.'.

how do I solve this?