cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dribbler31
New Member

SQL server "Get rows" OData filter query datetime less/equal

Hello everyone,

 

I'm very frustrated, because of trying something very simple, which won't work. I read countless threads and almost tried every single advice. Therefore I hope someone here could help me out.

 

My goal is to filter specific records of a SQL table. I want to get all records, where the value of a datetime column is between today and the next 14 days.

 

This is what I'd like to get to work.

1

But I always end up having issues like this one below.

2

 

I am very thankful for every advice!

 

Thanks in advance.

1 REPLY 1
Pstork1
Dual Super User III
Dual Super User III

When you do your FormatDateTime() I would suggest leaving it in UTC time rather than converting it to the local timezone.  If you are storing a Date/Time in a reqular SQL DataTime column its assuming that you are storing the UTC time.  To store an actual local time you need to include a TimeZoneOffset datatype in SQL. So its complaining that you are trying to compare a UTC datetime with a timezone specific DateTime without including the timezoneoffset.  So I would let you DAteTimeFormat process without specifying a format which will format the date as an ISO 8601 UTC date time.  That is what the SQL server wants anyway.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,475)