Date Issue

[remarsha]

Hi community, from a table with many columns, containing data for many months,  i need to pull out the data for the latest month&Year.

The dates (DateTime format) are available in the format as below. How do i pick up the records with the latest Month-Year for .e.g 11 2022

4/2/2020 0:00
4/3/2020 0:00
5/12/2020 0:00
7/6/2020 0:00
5/31/2021 0:00
6/15/2021 0:00
6/22/2021 0:00
6/29/2021 0:00
7/5/2021 0:00
7/7/2021 0:00
7/12/2021 0:00
7/13/2021 0:00
7/20/2021 0:00
7/28/2021 0:00
7/29/2021 0:00
8/2/2021 0:00
8/3/2021 0:00
8/4/2021 0:00
8/5/2021 0:00
8/9/2021 0:00
8/10/2021 0:00
8/11/2021 0:00
8/12/2021 0:00
8/13/2021 0:00
8/17/2021 0:00
8/16/2021 0:00
8/19/2021 0:00
8/23/2021 0:00

11/13/2022 0:00

11/21/2022 0:00
11/10/2022 0:00
11/11/2022 0:00
11/12/2022 0:00

[OllieClarke]

Hi @remarsha 

Firstly parse your datetimes into the proper format (yyyy-mm-dd hh:mm:ss)
Then pull out the year/month using DateTimeTrim([Date],'month')
Then sort Ascending
Then sample the last 1 grouped by year/month

Hope that helps,

Ollie

[OllieClarke · Accepted solution]

@remarsha 
Sorry, if you want to get the records from the latest year month combo, then :

do the same first 2 steps,
Find the max year month

Use a join to just keep the records which match this

Ollie

[remarsha]

Thanks Ollie. The solution is giving all the dates. I need only to pick up records with the latest month year, in this case its 11-2022. is that possible?

[OllieClarke]

Hi @remarsha 

My second post has you covered :)

Ollie

[remarsha]

Thanks will test and revert.

Just one Q. You have the taken Field1 as a  'String.'  in the beginning

For the data i have, its ' DateTime'. so do i need to covert it to string first and then follow the steps?

[remarsha]

i converted the original DateTime to String and followed the steps. It gives the follow error,

ConvError: DateTime (42): DateTime_Out: Cannot convert "2021-12-29 00:00:00" to a date/time with format "%m/%d/%Y %H:%M" and language "English": Month number is out of range 1..12: '2021-12-29 00:00:00' Record #7

[remarsha]

Hi Ollie, the second solution worked. Thank you very much. 

[OllieClarke]

Hi @remarsha 

I parsed the datetimes into the proper format based on your post. If you already have datetimes, then you can ignore the parsing part of the process, and start at the datetimetrim()

Ollie