Now I use code as below.
InputDT.AsEnumerable.Where(Function(x)x(“สถานะ”).ToString.ToLower.Equals(“นำจ่ายสำเร็จ”) And CInt(x(“จำนวนวัน”).Tostring) >= 15 And x(“APSTS1”).ToString <> "FLFL" And DateTime.ParseExact(x(“Issue Date”).ToString.Trim,“yyyyMMdd”,System.Globalization.CultureInfo.InvariantCulture).AddYears(-543)<= ApproveDate AndAlso String.IsNullOrWhiteSpace(x(“Status”).ToString) And String.IsNullOrEmpty(x(“Chk Dup”).ToString.Trim) And String.IsNullOrEmpty(x(“Lot Sent”).ToString.Trim)).ToArray
I want add condition as below.
I have variable DateReceived as string =18/1/2022
I want to filter excel column Issue date that <= DateReceived
*Remark : Format in column Issue date as yyyymmdd (yyyy = month in Buddhist calendar )
Please guide me for edit code LINQ.
ref.xlsx (8.9 KB)
input6.xlsx (58.2 KB)
1 Like
Hey @fairymemay
Is the Approve date a date time variable and which is format is it currently ?
Thanks
#nK
@Nithinkrishna yes approveDate as Datetime
variable approveDate = DateTime.ParseExact(ApproveDateStr,“dd MMM”,System.Globalization.CultureInfo.InvariantCulture)
1 Like
Hey @fairymemay
What’s the format of it - Thai date or normal date ?
Thanks
#nK
@Nithinkrishna I read ApproveDate after - and If today month3 —> I assume year now but if today month1 —> I assume previous year.
But use column Issue Date same.
1 Like
@Nithinkrishna Sorry I use column วันที่รับเล่ม
Not Approve Date
ref.xlsx (8.9 KB)
1 Like
Hey @fairymemay
Kindly use this in your query condition,
DateTime.ParseExact(x("Issue Date").ToString, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture) <= Date.ParseExact(x("วันที่รับเล่ม (<=)").ToString.Trim,"dd/M/yyyy",System.Globalization.CultureInfo.InvariantCulture)
The above condition will only do the date column comparison as requested for other columns you can add along with this.
Thanks
#nK
@Nithinkrishna
If I don’t use column วันที่รับเล่ม
Because I read value already in variable DateReceived as string = 04/05/2022 (dd/mm/yyyy)
1 Like
Sorry, unable to understand. Kindly explain.
Thanks
#nK
@Nithinkrishna From your suggest
Date.ParseExact(x(“วันที่รับเล่ม (<=)”).ToString.Trim,“dd/M/yyyy”,System.Globalization.CultureInfo.InvariantCulture)
If I want read data in Date.ParseExact(x(“วันที่รับเล่ม (<=)”).ToString
Because I have variable Example 18/1/2022 as string
How to edit your code?
1 Like
Yes the above code converts string to date by itself.
That’s is what you need I believe ?
@Nithinkrishna How to use variableDateReceived in code?
1 Like
Okay @fairymemay Got it.
You can just replace this x(“วันที่รับเล่ม (<=)”).ToString.Trim
with your variable
Thanks
#nK
@Nithinkrishna I use follow your suggest but error as below.
1 Like
Hey @fairymemay
Kindly show the date value please which you are passing through that variable ?
Thanks
#nK
@Nithinkrishna String as below.
Hey @fairymemay
It’s due to the format conflict.
Replace the format from the above as MM/dd/yyyy
.
Hope this helps
Thanks
#nK
@Nithinkrishna If I want change from column Issue Date to column วันรับเล่ม as below.
If file excel column วันรับเล่ม as format text
How to edit code from below?
DateTime.ParseExact(x(“Issue Date”).ToString, “yyyyMMdd”, System.Globalization.CultureInfo.InvariantCulture)
1 Like
Hey @fairymemay
Please find the updated below,
ateTime.ParseExact(x(“วันรับเล่ม”).ToString, “dd/MM/yyyy HH:mm”, System.Globalization.CultureInfo.InvariantCulture)
Thanks
#nK
1 Like