JSON URL传递查询参数异常：日期格式不符致数据范围错误求助

Hi Antonio,

It sounds like you’ve nailed down the root cause already—that date format mismatch is definitely why the API is ignoring your FromDate parameter and returning full-year data instead of the last 4 months. Let’s get this sorted out quickly!

The Problem Breakdown #

Your current code generates a date in the dd/MM/yyyy format (like 29/07/2019), but the API expects dates in yyyy-MM-dd (e.g., 2019-07-29). Since the format doesn’t match, the API can’t parse the parameter correctly and falls back to its default behavior of returning all annual data.

The Solution: Force the Correct Date Format #

You just need to add a format specifier to the Date.ToText function to explicitly output the yyyy-MM-dd format that the API requires. Here’s the adjusted code:

initialDate = "&FromDate=" & Date.ToText(DateTime.Date(Date.AddMonths(DateTime.LocalNow(), -MonthParameter)), "yyyy-MM-dd")

Let me break this down for clarity:

• Date.AddMonths(DateTime.LocalNow(), -MonthParameter): Calculates the date 4 months (or whatever value MonthParameter holds) before today
• DateTime.Date(...): Strips off any time component to ensure we’re only passing a pure date value
• Date.ToText(..., "yyyy-MM-dd"): Forces the date to be formatted as year-month-day, which aligns perfectly with the API’s expected format
• The &FromDate= prefix ensures the parameter is correctly named for the API endpoint

Quick Validation Tip #

Before plugging this back into your full URL, test the date formatting part alone to confirm it’s working:

Date.ToText(DateTime.Date(Date.AddMonths(DateTime.LocalNow(), -4)), "yyyy-MM-dd")

This should return a string like 2024-05-29 (adjusted to your current date minus 4 months) instead of the dd/MM/yyyy format you were getting before.

Give this adjusted code a try—your API should now recognize the FromDate parameter and return only the data from the last 4 months. Let me know if you run into any other snags!

内容的提问来源于stack exchange，提问作者onit