I have a Template column that produces a date which I want to use as a filter in a Query, however the Query doesn’t recognize it as a date. Any recommendations?
Template pulls a date from a User-Specific date column.
After the first failed attempt of the query not recognizing the template date I used a Text to Date to see if I needed to format the value.
Even still the Query doesn’t recognize the value a date.
I would probably use a single value column over a template column because a template column is always going to return a text string value.
Your screenshot shows that you are using a Format Date column as opposed to a Text to Date column. Format Date is probably still returning a text string whereas Text to Date might return an actual datetime value.
How are you letting the users enter this piece of info?
Can you describe us about your use case? Can’t you just use the user profiles column in the query?
Ah yes you’re correct, when I try to use a Text to Date it returns an empty value. I just put the template column and current time into a Date Difference column and it returned the proper value, so that tells me the template column is putting out an actual date value.
How would I set up a single value column? The dates are in my user profiles table. I’m pretty new to single value columns and don’t quite know how to utilize them.
Actually a single value column won’t work in this particular case, but @ThinhDinh made a good point. Why are you trying to get the date into a template column when you can just use it directly from the user profile within the Query column.
This is irrelevant because I don’t think this is the right approach, but regarding this, did you specify the format of the date coming from the template column? It should be similar to how you configured the Format Date column, but opposite.
I still haven’t found a solution to the posted issue, but I found a more efficient way to achieve what I wanted.
I’m using a Date Difference column with the week the progress report was submitted and the Date Picker. And then I have a Date Difference for the week of the progress report and current date. So then in the query the date diff of the week of the progress report is less than or equal to the date diff of the date picker.
Another consideration is to convert your dates into numbers.
For example:
YEAR(Date)*10000+MONTH(Date)*100+DAY(Date)
to get a number in YYYYMMDD format which is easy to use to compare as a number.
Or, if you are looking for week comparisons, this would work too.
YEAR(Date)*100+WEEKNUM(Date)
which gives you number in YYYYWW format.
