date time - subtract one hour

[RitaToubia]

I  have this datetime field, how do I subtract one hour? 

1/1/2022 12:00:14 AM

I can't figure out how do it, everything I tried doesn't change the date by one day, only the hour

[ShankerV]

hi @RitaToubia 

I request you to use the regex tool to extract only hour 12, 01, 02 etc.

The use formula tool if extracted value = 12 then change date and hour -1, 

else change hour -1

You will get the result you are expecting.

Many thanks

Shanker V

[ShankerV]

Hi @RitaToubia 

Noticed as you are using AM and PM in the time right, then 12 will appear in both AM and PM

Hence use regex tool to extract both hours and AM separately

Use formula matching condition if =12 AND =AM

then -1 both days and hours

else -1 only hours

If you are new to regex, use regex101.com which will be helpful or use left and right formula to extract hours and AM.

Many thanks

Shanker V

[DataNath]

Hey @RitaToubia, Alteryx deals with DateTime in ISO format (YYYY-MM-DD HH:MM:SS) and so, in scenarios like this, where yours is coming in a different format, we first need to parse this and we can do so in a couple of ways. My preference is to use the DateTimeParse() function within a formula expression, where we tell Alteryx the format of the incoming DateTime. Once we have this in a format that Alteryx can recognise and work with, we just use the DateTimeAdd() function to add -1 hours (takeaway an hour), before then wrapping all of this in the DateTimeFormat() function to get things back into your original format, that I'd imagine is your preference? The approach looks like this - I have split the 3 functions mentioned above :on to different lines to hopefully demonstrate what's going on more clearly:

DateTimeFormat(
DateTimeAdd(
DateTimeParse([Input],'%d/%m/%Y %I:%M:%S %p'),
-1, 'hour'),
'%d/%m/%Y %I:%M:%S %p')

Note: In the date provided it's impossible to tell whether the date is MM/DD/YYYY or DD/MM/YYYY as 1/1/2022 can be either. In my example I have assumed DD/MM/YYYY. However, if your date is actually MM/DD/YYYY then use the following (just switching %d and %m):

DateTimeFormat(
DateTimeAdd(
DateTimeParse([Input],'%m/%d/%Y %I:%M:%S %p'),
-1, 'hour'),
'%d/%m/%Y %I:%M:%S %p')

Hope this helps! Please feel free to ask any questions or shout up if you need anything else.

[binuacs]

@RitaToubia One way of doing this with the DateTime tool