How to fetch rows from SQL database based on date and time

(0) Share

Report

Posted on by Community Power Pla...

Hi Team,

I am trying to fetch data from the SQL Server table based on date. The datatype of Start_Date column is datetime.

I have inserted a data table component in my power app and in the items property and i have mentioned the below code.

Filter('[dbo].[TestTable]',Start_Date=Now()))

But it is not helping me to fetch the data

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (0)

v-bofeng-msft on at

Like (0)

Report
Hi @Anonymous :

The Now() function returns the current time, and its value is accurate to the second.

Could you check if there are records in [dbo].[TestTable] where the value of Start_Date is exactly equal to the current time?Could you provide some screenshots?

If you just want to get the value of Start_Date as today’s record,I suggest you try this formula:

Filter( '[dbo].[TestTable]', Start_Date>Today() && Start_Date<DateAdd(Today(),86399,Seconds) )

Best Regards,

Bof

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@v-bofeng-msft, thanks for your reply.

I have used the code but did not work in my case. I am trying to pull the data from SQL server database based the on the dates selected by the users in two date picker control "From Date" and "To Date". The data type of the column is "datetime" and database is also having different time zone. I want to pull the data only based on date and ignore the time part.

Was this reply helpful? Yes No
v-bofeng-msft on at

Like (0)

Report
Hi @Anonymous :

In my understanding, in theory, there is no pure date type data in CanvasApp. Even Today()(Or datapicker selected value) contains time (except that the time is 0 o'clock)

Because Now() contains a more specific time, I think your formula cannot work normally.

Maybe you could try:

Filter('[dbo].[TestTable]',Start_Date=Today()))

or

Filter('[dbo].[TestTable]',Text(Start_Date,"[$-en]yyyy/mm/dd")=Text(Now(),"[$-en]yyyy/mm/dd")) /*This formula is not delegable, I still recommend you to use the solution I provided before*/

Best Regards,

Bof

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@v-bofeng-msft
It is still not working. Do i need to convert date picker value into database time zone because it is working fine when do it in c#. If yes, please let me know how can i do that?

Was this reply helpful? Yes No
v-bofeng-msft on at

Like (0)

Report
Hi @Anonymous :

Are you saying that the value in tart_Date is time data with a date offset?

Then its data type should be ‘datetimeoffset’ instead of ‘datetime’, right?

It’s okay, I did a test for your reference:

1\My data table

2\Connect to the database

I noticed that even if I set the date offset, the time I get in the APP is still UTC time

3\Add a date picker control (DatePicker2)

Test1:

Filter('[dbo].[InzmamTable]',Text(Start_Dat2,"[$-en]yyyy/mm/dd")=Text(DatePicker2.SelectedDate,"[$-en]yyyy/mm/dd"))

Test2:

Filter('[dbo].[InzmamTable]',Text(Start_Dat1,"[$-en]yyyy/mm/dd")=Text(DatePicker2.SelectedDate,"[$-en]yyyy/mm/dd"))

Get the same result

In addition,If you want users to filter UTC time using time with date offset, you can first convert the time with date offset to UTC time. I assume that the user wants to use Now() in Beijing time as the filter condition,the formula should be:

Filter('[dbo].[InzmamTable]',Text(Start_Dat2,"[$-en]yyyy/mm/dd")=Text(DateAdd(Now(),-8,Hours),"[$-en]yyyy/mm/dd"))

Best Regards,

Bof

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi @v-bofeng-msft :
Solution you have given is working for me for year 2018 but it is not working for 2021. This is very surprising to me how PowerApps is handling the dates and making difference. In my table i am having data for the date 31-12-2018 but powerapps is not able to display those data in data table 1 control.

Was this reply helpful? Yes No
v-bofeng-msft on at

Like (0)

Report

Hi @Anonymous :

I have done many tests without encountering this problem.Maybe you can try to use the previous version of power App.

I addition,I suggest you consider creating a support ticket.

Best regards，

Bof

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@v-bofeng-msft

Sure i will try that
Please let me know from where can i create the ticket, just in case i need to create support ticket

Was this reply helpful? Yes No
v-bofeng-msft on at

Like (0)

Report

Hi @Anonymous :

If you want to create a support ticket please try:

1\login the Power Platform admin center

2\Select "Help + Support"

3\Select ""+ New Support Request"

Best Regards,

Bof

Was this reply helpful? Yes No