You are viewing limited content. For full access, please sign in.

Question

Question

date match without year

asked on March 9, 2020

This workflow worked 2 weeks ago.  I'm not sure if I broke it or it was system administrator that recently changed some naming terminology on the external tables.  

 

The workflow is suppose to query employees in an external database and find the employees hire anniversary date excluding the year.  So I was hired on 9/22/1997 so on 9/22/2020 an email is sent to have them review their information on file.  9/22 has to equal 9/22 of any year.  There could and is more than one employee on any given day. 

 

It doesn't get any results from the "Employee Review" query so it never does the "For Each Row" activity.  I have been messing with it all morning with no luck.  Any help?

 

 

wf1.png
wf2.JPG
wf3.JPG
wf4.JPG
wf1.png (12.16 KB)
wf2.JPG (36.71 KB)
wf3.JPG (80.5 KB)
wf4.JPG (38.63 KB)
0 0

Answer

SELECTED ANSWER
replied on March 10, 2020 Show version history

The reason I was asking what data type was used on the SQL side is that date and datetime are formatted in a standard way no matter how the date was sent over. For example, if you store 01/20/2020 as a "date" type, it is still stored as 2020-01-20 in SQL.

However, the fact that any of your queries worked in testing suggests that they're being stored as string values (not necessarily best practice), but that would be why the "LIKE" queries worked at any point.

 

Second, "LIKE" is not a partial comparison by default, you need a % symbol to act as a wildcard for it to work the way you expect and that was missing from your second round of tests.

For example,

'3/10/2020' LIKE '3/10/2020' -> TRUE

'3/10' LIKE '3/10/2020' -> FALSE

'3/10%' LIKE '3/10/2020 -> TRUE

Note that in your original screenshots the % is added to the end of the "HireDay" Token itself, but you did not have it in there when you tested 3/10.

Try testing '3/10%' instead of '3/10' and see if you get different results.

Also, I'd recommend adding the % in the query, not in the token value, that way it is easier to notice it should be there.

You could add it to the end of the "parameter values" or you could build it into the query itself so there's no way to forget it even in testing, like so,

WHERE [HIRE DATE] LIKE CONCAT(@today,'%')

Then your Parameter Value for @today would just be %(HireDay) and you could remove the extra % at the end when setting the HireDay token.

0 0

Replies

replied on March 9, 2020

I think the first step is to determine how the dates are actually being stored (i.e., what data type).

For example, [date] is formatted as YYYY-MM-DD and [datetime] is YYYY-MM-DD hh:mm:ss[.nnn]

"LIKE" is for string comparison (varchar, nvarchar, etc.), so I wouldn't expect that to work with an actual date/datetime.

When comparing specific portions of a date, you're probably better off using DATEPART

0 0
replied on March 10, 2020 Show version history

The dates are selected from the date picker on a LF Forms and are stored as M/DD/YYYY or MM/DD/YYYY.

 

When I replace the HireDay token with todays date I return one record like I should (See pic).  But when I change the year to the previous year it returns nothing and yet it should return the same record since the month and day match (See pic).  I also when I add a leading zero in from of 3 (March) nothing returns.

 

My Hire Date does return the value of 3/10 and were using the LIKE when this workflow was working fine for over 6 months.  

WF5.JPG
WF6.JPG
WF7.JPG
WF5.JPG (55.43 KB)
WF6.JPG (47.2 KB)
WF7.JPG (44.26 KB)
0 0
SELECTED ANSWER
replied on March 10, 2020 Show version history

The reason I was asking what data type was used on the SQL side is that date and datetime are formatted in a standard way no matter how the date was sent over. For example, if you store 01/20/2020 as a "date" type, it is still stored as 2020-01-20 in SQL.

However, the fact that any of your queries worked in testing suggests that they're being stored as string values (not necessarily best practice), but that would be why the "LIKE" queries worked at any point.

 

Second, "LIKE" is not a partial comparison by default, you need a % symbol to act as a wildcard for it to work the way you expect and that was missing from your second round of tests.

For example,

'3/10/2020' LIKE '3/10/2020' -> TRUE

'3/10' LIKE '3/10/2020' -> FALSE

'3/10%' LIKE '3/10/2020 -> TRUE

Note that in your original screenshots the % is added to the end of the "HireDay" Token itself, but you did not have it in there when you tested 3/10.

Try testing '3/10%' instead of '3/10' and see if you get different results.

Also, I'd recommend adding the % in the query, not in the token value, that way it is easier to notice it should be there.

You could add it to the end of the "parameter values" or you could build it into the query itself so there's no way to forget it even in testing, like so,

WHERE [HIRE DATE] LIKE CONCAT(@today,'%')

Then your Parameter Value for @today would just be %(HireDay) and you could remove the extra % at the end when setting the HireDay token.

0 0
replied on March 10, 2020

Yep you were right!  I tested 3/10% and it returned a record.  I added the wildcard behind the token in the Parameter Value and the workflow worked correctly!

 

I still wonder way it worked before and broke.  I had that wildcard in the Token itself.  

 

I appreciate the help Jason thanks!  

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.