cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vshah2
Helper I
Helper I

find the most recent timestamp

Hello Team,

 

I have a column in SQL table called timestamp as string in the format like 05/19/2020 08:47:10

 

I have a similar column in collection with same exact format. I want to compare both these time stamps to find out which one is most recent. Can someone please help?

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @vshah2 ,

How do you want to compare the timestamp between your SQL Table and your collection?

Do you want to find the latest timestamp between your SQL Table and your collection?

 

I agree with @Drrickryp 's thought almost, there is no direct way to compare the timestamp string value between the SQL Table and collection. You need to convert the timestamp string value into a actual date time value, and then you could compare the converted date time value.

 

In order to find the latest timestamp value in SQL Table, please try the following formula (set Text property of a Label to following):

First(
     SoftByColumns(
                 AddColumns('[dbo].[SQLTable]', "Timestamp_DatetTime", DateTimeValue(timestamp)),
                 "Timestamp_DatetTime",
                 SortOrder.Descending
     )
).timestamp

 

In order to find the latest timestamp value in your collection, please try the following formula:

First(
     SoftByColumns(
                 AddColumns(CollectionTable, "Timestamp_DatetTime", DateTimeValue(timestamp)),
                 "Timestamp_DatetTime",
                 SortOrder.Descending
     )
).timestamp

 

If you want to compare the latest timestamp value in your SQL Table and your collection table, please try the following formula (set the Text property of a Label to following):

If(
   First(
     SoftByColumns(
                 AddColumns('[dbo].[SQLTable]', "Timestamp_DatetTime", DateTimeValue(timestamp)),
                 "Timestamp_DatetTime",
                 SortOrder.Descending
     )
    ).Timestamp_DatetTime >
    First(
         SoftByColumns(
                 AddColumns(CollectionTable, "Timestamp_DatetTime", DateTimeValue(timestamp)),
                 "Timestamp_DatetTime",
                 SortOrder.Descending
        )
    ).Timestamp_DatetTime,
    "The Lastest Timestamp value is in SQL Table",
    "The Lastest Timestamp value is in Colelction"
)

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Drrickryp
Super User
Super User

Hi @vshah2 

You will need to use the DateTimeValue() function to convert your date and time.  Check the documentation to see how to do it. https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-datevalue-timevalue 

You would use DateDiff(DateTime1,Today(),Minutes) and DateDiff(DateTime2,Today(),Minutes) to compare the two values.

v-xida-msft
Community Support
Community Support

Hi @vshah2 ,

How do you want to compare the timestamp between your SQL Table and your collection?

Do you want to find the latest timestamp between your SQL Table and your collection?

 

I agree with @Drrickryp 's thought almost, there is no direct way to compare the timestamp string value between the SQL Table and collection. You need to convert the timestamp string value into a actual date time value, and then you could compare the converted date time value.

 

In order to find the latest timestamp value in SQL Table, please try the following formula (set Text property of a Label to following):

First(
     SoftByColumns(
                 AddColumns('[dbo].[SQLTable]', "Timestamp_DatetTime", DateTimeValue(timestamp)),
                 "Timestamp_DatetTime",
                 SortOrder.Descending
     )
).timestamp

 

In order to find the latest timestamp value in your collection, please try the following formula:

First(
     SoftByColumns(
                 AddColumns(CollectionTable, "Timestamp_DatetTime", DateTimeValue(timestamp)),
                 "Timestamp_DatetTime",
                 SortOrder.Descending
     )
).timestamp

 

If you want to compare the latest timestamp value in your SQL Table and your collection table, please try the following formula (set the Text property of a Label to following):

If(
   First(
     SoftByColumns(
                 AddColumns('[dbo].[SQLTable]', "Timestamp_DatetTime", DateTimeValue(timestamp)),
                 "Timestamp_DatetTime",
                 SortOrder.Descending
     )
    ).Timestamp_DatetTime >
    First(
         SoftByColumns(
                 AddColumns(CollectionTable, "Timestamp_DatetTime", DateTimeValue(timestamp)),
                 "Timestamp_DatetTime",
                 SortOrder.Descending
        )
    ).Timestamp_DatetTime,
    "The Lastest Timestamp value is in SQL Table",
    "The Lastest Timestamp value is in Colelction"
)

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,815)