convert String Date Time to Date&Time

[Subramanyam]

Hi Team,

I would like to convert the below format String Date&Time 1/1/1980 12:00:00 AM
to Data&time using the String to Date&Time node but unable to convert it.
Please help me in resolving it.

Thanks,
Subbu.

[nan]

Hi @Subramanyam ,

this pattern might suit your data: “M/d/yyyy h:mm:ss a”

Have a nice day,
nan

[Subramanyam]

Hi @nan

Unfortunately couldnt suitable for my data.

[takbb]

Hi @Subramanyam , there are some things to add. The format mask that @nan gave, could well be correct, but as you haven’t told us what the order of month and day is in your date, it could equally begin with M/d or d/M.

A second thing to be aware of is the fact AM/PM are upper case.

Unfortunately the standard Date&Time nodes are case sensitive and whether it is AM/PM or am/pm is “locale dependent”, so you will need to make sure you set the “Locale” on the String to Date&Time node to a locale that accepts the upper case version.

This can be trial/error but as a quick example, with the locale set to en-US, the “a” element of the mask will convert AM/PM but fail am/pm
With the locale set to en-GB, the “a” element will convert am/pm but fail AM/PM

You will hopefully get what you need from the above.

Alternatively, I have a component “Parse Date&Time” which will quickly convert many different date/time strings if you simply tell it the order of the day month and year elements.

More details of that component can be found here:

Date Formats

I have a component which can save a lot of time for many date formats Using it once to handle dates in dmy sequence and again for dates in mdy sequence, and finally merging the two outputs can get the results quite quickly and with minimal config requirements [image] Provided there are no superfluous extra characters, it simply needs to be told in what order date month and year will appear, and it does the rest: [image] It has a choice of two locales. I use en-US as primary and en-GB as s…

Additional background info re AM/PM handling and parsing:

Variable date/time format with am/pm

@WilliamStewart welcome to the KNIME forum. Di you have a look at this example and the linked discussions?

String to Date&Time worked on previous knime version but having issues with current version (4.4.1)

BUT… in spite of my previous reply you are right @Sajith !! I was surprised about the case of the AM/PM, as I thought it should be upper case, so I just went and tried it in KNIME 4.3.4 Using the format mask dd/MM/yyyy h:mm a KNIME 4.3.4, converts 16/09/2021 6:07 PM KNIME 4.4.1 converts 16/09/2021 6:07 pm They both complain about the opposite case. (I was testing this using locale en-AU as per the screenshot. The result of other locales may vary I guess) Likewise the “a” in the “Date & T…

[Subramanyam]

Hi @nan , @takbb ,

It didnt helped me out to configure the issue.
Please help me out.

Thanks,
Subbu

[ScottF]

Hi @Subramanyam,

Please provide some additional information - ideally error messages and example data. We don’t have much to go on here - “it doesn’t work” isn’t enough detail to help you resolve the problem.

[forum]

Internal ticket ID: UIEXT-2801
Summary: String to Date&Time: Add common US pattern “M/d/yyyy h:mm:ss a” for more flexible auto-guessing
Fix version(s): 5.5.0