Date minus one day and no time

RPeeters

How can I have a date minus one day without any time value?

DatetimeAdd(ToDate(DatetimeNow()),-1,"days")

This gives me yesterdays date but with 00:00:00 behind it.

DateTimeFormat(DateTimeToday(),"%Y-%m-%d")

This gives me the date without time but I cannot adjust it for -1 day.

The output that I'd like to see for 2020-05-27 (today) is 2020-05-26.

Date Time

Accepted answers

fmvizcaino

Hi @RPeeters ,

To remove the time you can use a select tool and format the data type to Date format, that way the time will be automatically removed. Then you can use the filter as you built.

One last thing, you don't need to use the datetimeformat function to format dates to yyyy-mm-dd, this is the standard format for dates in alteryx.

Example attached.

Best,

Fernando Vizcaino

2020-05-27 Date minus one day and no time.yxmd

All comments

fmvizcaino

Hi @RPeeters ,

You need to select date as data type, that way you will eliminate the hours.

Best,

Fernando Vizcaino

RPeeters

Thanks @fmvizcaino however how does this work in a filter?

I have 3 building blocks:

input > convert to data > filter > workflow continues

Input from database

Date Ordernumber

2020-05-20 08:15:14 120002910
2020-05-20 08:15:14 120002910

Date time convert:

Date/time format to string

Date to Date1 column with yyyy-mm-dd

Date Ordernumber Date1

2020-05-20 08:15:14 120002910 2020-05-20
2020-05-20 08:15:14 120002910 2020-05-20

Filter

[Date1] = DateTimeFormat(DateTimeToday(),"%Y-%m-%d") AND STARTSWITH([ORDNUM],'12')

Thoughts:

It seems I either need to adjust the date time convert. The datatype of 'date' is datetime when it comes from the database but the convert only allows 'Date/time format to string'.

Or somehow convert it back to a datetime format type when/before it comes to the filter, which is probably inefficient.

input.JPG

JohnTowey

Here is another solution. Results in start date is today minus 1 day as well as end date is today's date formatted as yyyy-mm-dd. Example output: START_DATE = 2025-10-09, END_DATE = 2025-10-10. It also includes other date attributes such as MMM, mm, yyyy, yy and dd. My full flow utilizes these attributes for the SQL query as well as building out the folder and file names. Good luck.

AutomaticStart_End_Dates.yxmd

Automatic_Start_End_Dates.png

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703