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.

[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.

[fmvizcaino · Accepted solution]

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