Converting Varchar data into Datetime

Hi everyone,

I’ve been trying to convert data I’m receiving from a varchar (20171211 125023) into a datetime (2017-12-11 12:50:23). I’ve been messing with the mapper but I feel like I’m at a road block now. I was wondering if someone in the community has any success in this.

image

Hi Matt
You can use Date.parse() function to convert input string to Datetime.

Date.parse(“20170604 125452”, “yyyyMMdd HHmmss”)

this will give desired output.
convertVarcharToDate_2018_12_06.slp (2.6 KB)

Hi Matt,

You can try with the below expression in your mapper. Hope this helps

Date.parse(“20170617 125254”,“yyyyMMdd hhmmss”).toLocaleDateTimeString({“format”:“yyyy-MM-dd hh:mm:ss”})

The output value will be

image

Hey Pavan,

Thank you for the reply, I just have a quick question. If I have a input scheme for example $DATE that’s bringing in data, would I just replace “20170617 125254” with the schema?

Yes, That’s correct.

When I insert my schema this is what I get.

Could not compile expression: Date.parse($MODDATE,“yyyyMMdd … (Reason: Invalid token: ‘“’ for expression: Date.parse($MODDATE,“yyyyMMdd …; Resolution: Please check expression syntax)

Can you give me the complete expression you are providing and the error as well.

Of course,

expression:
Date.parse($MODDATE,“yyyyMMdd hhmmss”).toLocaleDateTimeString({“format”:“yyyy-MM-dd hh:mm:ss”})

Validation errors:
property_map.settings.transformations.value.mappingTable.value[2].expression.value: Could not compile expression: Date.parse($MODDATE,“yyyyMMdd … (Reason: Invalid token: ‘“’ for expression: Date.parse($MODDATE,“yyyyMMdd …; Resolution: Please check expression syntax)

Matt,

If you copy the expression from here, it will have different quotations (" "). Try to type the entire expression in the mapper instead of copying it from here. It works.

The quote thing messed me up when I was trying to help someone with an expression on a different thread. If you wrap the expression in preformatted text using image, then it won’t change the quote mark.

For example I think this should copy and paste ok:

Date.parse($MODDATE,"yyyyMMdd hhmmss").toLocaleDateTimeString({"format":"yyyy-MM-dd hh:mm:ss"})

1 Like