Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Converting time stamp field

iRm
6 - Meteoroid

Hi,

I have a situation where I have 2 fields.

One field is coming in as for example -  '2017-07-07 00:40:08'

I want to be able to convert it so that it becomes '2017-07-27 00:00:00'.

I have searched on the community but I am not able to find the function that would zero out the hours/minutes/seconds. I will continue to look but if anybody knows about it, please let me know. Thanks in advance!

 

 

8 REPLIES 8
Kenda
16 - Nebula
16 - Nebula

@iRm one option would be to create a new string field with the following expression: REGEX_Replace([Field1], "(.*)\s(\d\d\:\d\d\:\d\d)", "($1) 00:00:00"). Then, if you want it to be a DateTime field, just add a DateTime tool to convert it. Another thing you could do (if you just want the date and no time after, resulting in a field that is 2017-07-07) would be to just add a Select tool after your input and change the field type to 'Date'. Hope this helps!

danrh
13 - Pulsar

You should be able to use DateTimeParse([YourData],'%y-%m-%d')

 

Hope it helps!

adm510
11 - Bolide

Use a select tool and convert to Date, then convert back to DateTime :)

While this would work, I would use @danrh's formula

KaneG
Alteryx Alumni (Retired)

Also, DateTimeTrim([Field1],'day')

suli
9 - Comet

Hi @iRm,

 

I would use a multi field formula, because it enables to change field type, target field type is DateTime.

If your field is string, use, datetimeparse([Field], "%Y-%m-%d 00:00:00")

 

If it is already DateTime, use a normal formula with datetimeparse([Field], "%Y-%m-%d 00:00:00") function.

 

Also if you force field type to Date, it will be only date (time will get trimmed), but for any calculation default hour is still 00:00:00.

 

Hope it helps!

iRm
6 - Meteoroid

Thanks everyone!  I used the Select tool and it was sufficient for my purposes.

iRm
6 - Meteoroid

Thank you. Is there a reason why I would use the formula instead of the select tool?

danrh
13 - Pulsar

No, not particularly. The only advantage I can think of would be that in order to keep the hours and minutes, you would need two select tools (one to change it to date, the other to change it back to date/time), vs. only one formula tool to strip out the hours and minutes.  If you aren't particular about date vs date/time format, than both methods will only take one tool.

 

Glad you got it working!

Labels