Forum Discussion
Dynamic Date function as parameter in JSON data source not working
Thanks we are 1/2 way there now!
I changed yours to add the Zone, since it requires a DateTimeZone format but it is different zone format
AND no .format is available.
so i need a way now to format the zone string in this format.
{date.format("yyyy-MM-dd'T'HH:mm:ssZ").tostring()}
GetTodayDate = Text.From(DateTimeZone.LocalNow()),
SevenDaysAgo = Text.From(Date.AddDays(DateTimeZone.LocalNow(),-7)),
DataSource.Error: Web.Contents failed to get contents from 'http://secret URL/select?defType=lucene&facet=false&q=orderDate:%5B5/12/2020%202:25:36%20PM%20-05:00+TO+5/19/2020%202:25:36%20PM%20-05:00%5D&rows=50000&sort=orderDate+desc&start=0&wt=json' (400): Bad Request
that seems to work for the first part because it does put a date in, however the format is wrong.
5/12/2020%202:25:36%20PM%20-05:00
2020-05-07T00:00:00.000Z
It has to use dashes and THIS DateTimeZone format. do you know of a .format command that works with DateTimeZone?
Community ChampionIs the time that yopu want always 00:00:00.000? If so, use this:
GetTodayDate = Text.Replace(Text.Start(Text.From(DateTime.LocalNow()),10),"/","-") & "T00:00:00.000Z",
SevenDaysAgo = Text.Replace(Text.Start(Text.From(Date.AddDays(DateTime.LocalNow(),-7)),10),"/","-") & "T00:00:00.000Z"Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs
Cheers
Getting really close. I think it is flipping the format of the date around now.
5-12-2020 T00:00:00.000Z TO 5-19-2020 T00:00:00.000Z
needs to be:
2020-05-12T00:00:00.000Z TO 2020-05-19T00:00:00.000Zis there no date format, since you are doing text replaces?