Date conversion

studio

#1

Dear all,

I would like to convert a Date to another format.
I already read some other topics about this, but still I am not very good at this.

The format is as such:
Wednesday, July 26, 2017 - 7:12 AM EDT

I would like to convert it to
26/07/2017

I need the day, month and year separated so I will split them afterwards.

I removed "Wednesday, " and " - 7:12 AM EDT"
so “July 26, 2017” remains. It is not a very unusual format so it shouldn’t be very hard to convert it.

I already tried this:
Convert.ToDateTime(Td4.ToString(“dd/MM/yyyy”))

Td4 is the Generic Value variable.

When I do this, the error “Assign : The string was not recognized as a valid DateTime. There is an unknown word starting at index 0.” is shown.

Is there anyone that can help me with this? There are really similar topics but still I can’t get it done.
Thank you in advance!


#2

Date.ParseExact(Td4.ToString,“MMMM dd, yyyy”, CultureInfo.InvariantCulture)


#3

Hi @trabart

You can assign new CultureInfo(“es-ES”) to System.Threading.Thread.CurrentThread.CurrentCulture
to the beginning of your workflow and then use DateTime.ToShortDateString function.

image

Regards,
Susana


#4

Thanks, but I guess that is only when you want the date of as we speak. But it also needs to work for much older dates, for example “Thursday, March 4, 2016”.


#5

Thank you for your fast reply.

I tried it, but I have problems with the CultureInfo.

I checked this thread and added some namespaces, but a problem popped up as shown in the image below.


#6

Hello trabart,

You need to import the namespace System.globalization

or (do this only if you know what you are doing )

add the XAML Line using a text editor (Atom, or notepad ++)

<x:String>System.Globalization</x:String>

image


#7

I already did that!


#8

This solution is not the most ideal, but the string manipulation works if you do it like this:
dateString of type String = “Wednesday, July 26, 2017 - 7:12 AM EDT”

dateVar of type DateTime =
cdate((dateString.Split(","(0))(1)+dateString.Split(","(0))(2).Split("-"(0))(0)).Trim)

then, dateVar.ToString(“MM/dd/yyyy”) outputs the correct format

Thanks


#9

Awesome, I see that the reason for this is that the date has to be in this format : 20170726

and according to the following info, this can be possible with

 // e.g. format = "dd/MM/yyyy", dateString = "10/07/2017" 
var formatInfo = new DateTimeFormatInfo()
{
  ShortDatePattern = format
 };
 date = Convert.ToDateTime(dateString, formatInfo);

#10

To avoid errors you should use TryParseExact first.

Note that the byRef value at the end which assigns the value to a date variable does not work so you need to follow the TryParseExact with a ParseExact (fewer parameters)


#11

Of course it is a way to bypass the failed recognition of the date, but I still like the idea.
It works like a charm.
For the other ideas I will have to take some more time to check how they work, I think they are rather complicated but I would like to understand it.


#12

Date.ParseExact("July 26, 2017","MMMM dd, yyyy",Nothing).ToString("dd-MM-yyyy")


#13

EDIT:

Sorry, mate,

I asked you a question but I already found a solution for my problem and now I understand the logic behind your solution!

Thank you!


#14

I guess that “July 26, 2017” resembles the variable that contains the date, doesn’t it? I do not understand what the function is of the date inside the expression.


#15

No Problem. The extra zero in the .Split was to convert the string to a character since the parameters require a character array. There are a few ways to do it also, like .Split("-"c) or .Split({"-"},System.StringSplitOptions.RemoveEmptyEntries which is useful to know, and my logic was basically to just cut the string in parts and grab the part that contains the date, assuming the structure is consistent.

If you want something more reliable you might consider Regex too.
Here is an example that I got working:
Date.ParseExact(System.Text.RegularExpressions.Regex.Match(“abcdef August 10, 2017 - abcdef”, “([A-Za-z]{10}|[A-Za-z]{9}|[A-Za-z]{8}|[A-Za-z]{7}|[A-Za-z]{6}|[A-Za-z]{5}|[A-Za-z]{4}|[A-Za-z]{3}) ([0-9]|[0-9]{2}), [0-9]{4}”).Value,“MMMM dd, yyyy”,Nothing).ToString(“dd-MM-yyyy”)

If the format of the date changes the Regex expression might need adjustments. I normally use CDate() instead of ParseExact to be honest.

Thanks!


#16

You might want to change your habits… (VBA CDate() and VB.Net CDate() behave very similarly)
https://stackoverflow.com/questions/29968276/inconsistent-behaviour-of-cdate

Fun bonus (if you ever read a decimal value from Excel as String and used CDate):
CDate("1.27").ToString => 01/27/2017 00:00:00
Basically CDate will try to “guess” what the date is, f.e. if one part is missing, it will fill it from current date. IMHO handling date conversions is actually one of the easiest things to do wrong if you’re not careful. Heck, there are entire huge custom libraries that were written solely to correctly handle datetime objects (and they still have unsupported corner cases).


#17

@andrzej.kniola yeah good point. Thanks. Never even considered that the month,day, and year could be backwards depending on your culture. Will keep that in mind in the future.


#18

Date conversions are a bewilderment of complexity compounded by an absence of any understandable guidance. I can’t find any reference to the parameter “Nothing” in your expression “Date.ParseExact(“July 26, 2017”,“MMMM dd, yyyy”,Nothing).ToString(“dd-MM-yyyy”)”. I’d like to understand what it means and why it’s used.
Thanks,
AJ


#19

Hi Alan,

I know you don’t like MSDN links, but this time it will be hard without it (explanation below as well):
https://msdn.microsoft.com/en-us/library/w2sa9yss(v=vs.110).aspx

The .ParseExact method takes 3 parameters:

  • string that contains the date to parse
  • string with exact format that the date is in
  • IFormatProvider that contains culture information about the date (see Remarks on the MSDN page what you can pass there if you want).

Here @ddrdushy1 supplied Nothing meaning that he didn’t give any culture information. In that case, from MSDN:
If provider is null, the CultureInfo object that corresponds to the current culture is used.
In UiPath this actually means InvariantCulture (think US date styles).

IMHO this is actually a wrong approach, especially since it has a full month name. If it would be just numbers (14.09.2017), sure, but if someone runs the same workflow on a different machine, it can fail.
What I’d suggest as a best practice with anything culture-sensitive is to always specify the culture. In that case the code would be:
DateTime.ParseExact("July 26, 2017","MMMM dd, yyyy", CultureInfo.CreateCulture("en-US")).ToString("dd-MM-yyyy")

As far as I remember you’ve already went through CultureInfo so I’ll skip more details for it right now.

Unfortunately, as I wrote earlier in this topic, properly handling dates and time is difficult. Computers are just not good with it on their own and you need to be very specific or risk weird errors - this doesn’t regard just UiPath, but basically all programs.


Convert SAP date dd.mm.yyyy string to DateTime object
#20

One day everyone will use YYYYMMDD and the world will be a better place.