String to Date time conversion issue

Hi,

I am having excel data which contain date time however format is not consistent. At some place second part is not available. Once data is loaded to datatable, I am using below code to parse and saving in a date variable.

DateTime.ParseExact(row(“MyDateTime”).ToString.Trim,“dd/MM/yyyy HH:mm:ss”, System.Globalization.CultureInfo.InvariantCulture )

01/03/2019 08:03:15
01/03/2019 08:04:56
21/03/2019 23:05:52
04/12/2019 01:07
05/07/2019 20:29
05/08/2019 14:29
05/09/2019 19:12:12
13/05/2019 12:34:09
16/05/2019 14:03
31/05/2019 11:50
So whenever ss part is not available it’s failing .

Any suggestion on approach.

Hi @p4uk80
Try like this buddy
DateTime.ParseExact(row(“MyDateTime”).ToString.Trim.Substring(0,16),“dd/MM/yyyy HH:mm”, System.Globalization.CultureInfo.InvariantCulture )
you were almost done
Cheers @p4uk80

1 Like

Hi, would this help if you e.g. use a regex to recognize whether your date time is abbreviated or not, and then act accordingly - with “Datetime.parseexact(dateString,“dd/MM/yyyy HH:mm”,system.globalization.cultureinfo.invariantculture).ToString(“dd/MM/yyyy HH:mm:ss”)” what can be surely converted by Convert.ToDateTime? The regex that works for me in this instance is “(^\d\d\W\d\d\W\d\d\d\d\s\d\d\W\d\d\b)$”

any issues still buddy @p4uk80

Thanks @Palaniyappan. I checked and it worked like a charm.

1 Like

Fantastic
Cheers @p4uk80
Kindly close this topic buddy