Date as String - How to convert to Date

[brendafos]

I've spent some time reading other posts but I've not quite gotten the answer I need.....

The incoming data has a field type v_string which looks like 5/8/2015 -- I need to convert this to a type date.

I tried the Select tool changing v_string to date but that just outputs 'null's.

I tried -

DateTimeParse(
  REGEX_Replace([Text], "([a-z]{3})\s*([0-9]{1}),","$1 0$2,"),
  "%b %d,%Y")

But that gives 'null' and does not give an error.

Can I use DateTimeParse with a string type?

Or how is the best way to approach this? 

[jdunkerley79]

You need a leading zero on the day as well try:

DateTimeParse(
  RIGHT("0" + REGEX_Replace([Text], "([a-z]{3})\s*([0-9]{1}),","$1 0$2,"), 10),
  "%b %d,%Y")

[brendafos]

And... I already tried to use the Parse - DateTime conversion tool.

but it seems to do nothing - I put a Select after and it still show data type as v_string

[NickJ]

Hi there, 

Attached is a tiny workflow that takes a 'date' as a V_String and uses DateTimeParse to convert to a 'real' date (liberties taken on whether the month is May or August!)

Hope this helps?

Cheers,

Nick

- Solutions Engineer

[JohnJPS · Accepted solution]

The following worked OK for me:

DateTimeParse([dt],"%m/%d/%Y")

Tested successfull on:

• 1/1/2001
• 1/11/2001
• 11/1/2001
• 11/11/2001

[s_pichaipillai · Accepted solution]

@brendafos

try DateTimeparse([Field1],'%m/%d/%Y') using formula tool

[brendafos]

Thank you.

I tried you addition... but I still just get nulls

[NickJ]

Just noticed that you're using the DateTime mask %b (which is lowercase three-letter month name rather than a numeric month) - can you replace this with %m to see if it helps?

(Also - can you definitely confirm the format of the incoming V_String? Paste an example if possible?)

Cheers,

Nick

- Solutions Engineer

[brendafos]

NickJ and everyone.-  I am making progress.

I at least have a date now  -

Here's what I'm using..

DateTimeParse([Enc - Discharge Date],"%m/%d/%Y")

But I get an odd format of the output.. the new date column is Enc - Desc Date

Why is it YYYY-MM-DD ?

[jdunkerley79]

That is how Alteryx represents dates, as a string in yyyy-MM-dd format.

All the datetime functions operate on this