Convert to datetime format Error: Length cannot be less than zero (Parameter'length')

Help Studio
, ,
1

Hi there,

i have this pivot table and i need to convert it to datetime format to “MM/dd/yyyy”
this is my syntax that is not working anymore.
Assign variable ExtractedRowDate as System.DateTime =
DateTime.ParseExact(row(0).tostring.Substring(0,row(0).ToString.IndexOf(" "c)).Trim,“MM/dd/yyyy”,System.Globalization.CultureInfo.CurrentUICulture.DateTimeFormat)
Error Length parameter cannot be less than zero the result is -1
i would like to read only the date row and convert it to datetime format

image
image749×579 43.4 KB

1 Like
2

@YangYang

try this:

ExtractedRowDate

As variable type DateTime

ExtractedRowDate = If(DateTime.TryParseExact(row(0).ToString, "MM/dd/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, Nothing), DateTime.ParseExact(row(0).ToString, "MM/dd/yyyy", CultureInfo.InvariantCulture), DateTime.MinValue)

cheers…!

3

ExtractedRowDate is in DateTime variable type
the result did not write as per the pivot row(0)
image

4

@YangYang

ExtractedRowDate is a DateTime variable, to convert it back to a string in “MM/dd/yyyy” format, use:

ExtractedRowDateString = If(ExtractedRowDate <> DateTime.MinValue, ExtractedRowDate.ToString("MM/dd/yyyy"), "")

cheers…!

5

@YangYang Can you Provide Excel File
Then Some Hint To Some Logics.

6

Hello @YangYang

To convert a date string in a DataRow to DateTime format in “MM/dd/yyyy” format:

Assign ExtractedRowDate = DateTime.ParseExact(row(“YourDateColumnName”).ToString, “MM/dd/yyyy”, System.Globalization.CultureInfo.InvariantCulture)

Thanks & Cheers!!!

7

DateTime.ParseExact(row.ToString, “MM/dd/yyyy”, System.Globalization.CultureInfo.CurrentUICulture.DateTimeFormat)

Don’t Use Row(0)
Use row(“Row Labels”).Tostring

8

Hi sai_gupta,

my initial syntax was
DateTime.ParseExact(row(0).tostring.Substring(0,row(0).ToString.IndexOf(" "c)).Trim,“MM/dd/yyyy”,System.Globalization.CultureInfo.CurrentUICulture.DateTimeFormat)

which it result in parameter cannot be less than zero.

your syntax is
If(DateTime.TryParseExact(row(0).ToString, “MM/dd/yyyy”, CultureInfo.InvariantCulture, DateTimeStyles.None, Nothing),
DateTime.ParseExact(row(0).ToString, “MM/dd/yyyy”, CultureInfo.InvariantCulture),
DateTime.MinValue)

that give result to 01/01/0001 00:00:00
image

i need it to be in 05/05/2022 and 05/06/2022… 05 is Month 06 is day

not use to tryparse
was it meant if the row(0) is nothing
If(DateTime.TryParseExact(row(0).ToString, “MM/dd/yyyy”, CultureInfo.InvariantCulture, DateTimeStyles.None, Nothing),

then result is
DateTime.ParseExact(row(0).ToString, “MM/dd/yyyy”, CultureInfo.InvariantCulture),

else result will be
DateTime.MinValue)

?

1 Like
9

Please Debug This Point

I Understand Your Problem
Robot Will Run Plain Index
Like Blank Data Read
That’s Show Or Output As 01/01/0001 00:00:00

1 Like
10

ya but my pivot is first column is not blank but it read all as blank
image

11

Ok
Can We Share Your Project FIle.
Or Excel

13

Hii,

Here Final Solution,
In Your Case (But Use Also Other Developer Can Also reference)
–>1:
Check Excel Read Range Property IS Checked If Not Then
image

–>2:
image

if Only You Want Date Then
select System.DateOnly

–>3:
Solution

ExtractedRowDate = DateOnly.Parse(Convert.ToDateTime(row(“Date”).ToString.Trim).ToString(“MM/dd/yyyy”))

Output
image

if You Want Read And Write Then Reconvert Tosting

Thanks, @YangYang

1 Like
14

hi @Darshit_Vekariya

Will you be able to tell me what went wrong with my initial syntax
what can i add / change to make this syntax into reading the positive?
This outcome is -1 which has error saying Error Length parameter cannot be less than zero
DateTime.ParseExact(row(0).tostring.Substring(0,row(0).ToString.IndexOf(" "c)).Trim,“MM/dd/yyyy”,System.Globalization.CultureInfo.CurrentUICulture.DateTimeFormat)

thanks

15

Because All time Not Select Index of 0
That’s Why I’m Change Query or Syntex.

Note: This Way To Perfect Solution Than Why Select Other Way.
Ex:
int a = 1 ,b=2 ,c=3 ,d=4 ,e=5;

Task Simple Addition Than Two Way
First Simple Way Is:
Int Result= 0;
Result= a+b+c+d+e;

But You Select
Result= a+b;
Result= count+c;
Result= count+d;
Result= count+e;

Then Final Answer Result Is

Thanks And Sorry Any My Mistake.