community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Converting String to DateTime

Meteoroid
Hi,

I have a field with date being written as 'Thursday, July 30, 2015 12:00pm EDT', how do I go about converting it to a standard date time format with timezone information. I don't see any custom date formats in the DateTime Tool. I did try using the DateTimeParse function, but am not getting the desired output which should be something like '2015-07-30 12:00 PM -0500'.


Thanks,
Pratik Gandhi
Alteryx Certified Partner

Hi Pratik,

 

So it turns out this is a surprisingly frustrating question. I think the timezones are going to have to be custom unless anyone has experience with this? 

 

I've uploaded an example of the datetime parsing here. I've parsed out all the information at the start into seperate fields so if you find a lookup source for your timezones, you can add values after. The reference article for the time parsing information is located here. It's quite complete but I don't really trust it. Some of the options don't appear to work as expected.

 

Anyway, give it a go and let me know what you think.

 

Ben - BIPB

Highlighted
Alteryx Partner

Theres also two great posts by Paul Houghton on this:

 

http://www.theinformationlab.co.uk/2014/11/13/time-part-1/

http://www.theinformationlab.co.uk/2015/02/15/time-part-2/

 

Using his workflow (RFC date formula) along with a few changes (a bit of regex and formula tools) this will be possible, but as mentioned, surprinsignly a little frustrating due to TimeZone and am/pm (lowercase)

Atom

So there is no easier method to convert a date that I have inherited in flat files in the format "Sep  9 2015  1:37:08:000PM".  I am told that this is a standard format from Sybase IQ outputs.

 

Any suggestions other than using the painful substring/formula approach?

 

Thanks for reading.

 

Shaunak

ACE Emeritus
ACE Emeritus

I hate substring, in this situation I like to break up the data into building blocks using the text to column tool, then I just use a simple formula calculation to put the blocks together.

 

1) feed data into the text to colum tool, the delimter would be the space which would breakout the first part of your date, set the max break out to 3 fields and then put everything else in a 4th field

The result would be Sep 9 2015 would be in 3 fields

 

2) use the delimiter tool again on the output of the first one, but this time on the 4th field which would contain 1:37:08:000PM, this time set the delimter to :

 

3) I don't know what final format you need but just use a formula tool (field1) +'/'+ (field2)....... and so one. 

 

To help keep my head straight, I usually put a select tool after each text to column tool and rename the fields accordingly, otherwise you end up with 7+ field with generic headers.

A pretty custom but reusable technique is using a regular expression tool to break the text into components:

\s*([A-Z][a-z]{2})\s*([0-9]{1,2})\s*([0-9]{4})\s*([0-9]{1,2}):([0-9]{1,2}):([0-9]{1,2}).*([AP]M)

Using Output method of Parse produces 7 new columns:

Monthstring3
Daystring2
Yearstring4
HourInt16 
MinInt16 
SecInt16 
AMPMstring2

 

I then used a formula tool to merge the AMPM with the Hour:

IIF(AMPM='PM',12,0)+MOD([Hour],12)

 

Finally a formula to parse the fields into  DateTime field. Only slight catch is to add a leading 0 to the Day. 

DateTimeAdd(DateTimeParse(
    RIGHT('0'+[Day],2)+'-'+[Month]+'-'+[Year],'%d-%b-%Y'),
    ([Hour]*60 + [Min])*60 + [Sec], 'seconds')

A little convoluted but works pretty well. Test it on 674 different date time strings and seems to work

Labels