SOLVED

Issue with creating DateTime Condition in Microsoft Flow

%3CLINGO-SUB%20id%3D%22lingo-sub-4737%22%20slang%3D%22en-US%22%3EIssue%20with%20creating%20DateTime%20Condition%20in%20Microsoft%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-4737%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20create%20a%20recurring%20Flow%20which%20checks%20the%20difference%20between%20a%20date%20field%20%5BContractEndDate%5D%20and%20variation%20from%20todays%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20ContractEndDate%20is%20less%20than%20(todays%20date%20%2B%2015%20days)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELooking%20at%20the%20workflow%20definition%20language%20here%20(%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Flibrary%2Fazure%2Fmt643789.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fmsdn.microsoft.com%2Flibrary%2Fazure%2Fmt643789.aspx%3C%2FA%3E)%20i%20came%20up%20with%20the%20following%20logic%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%40less(item()%5B'ContractEndDate'%5D%2C%26nbsp%3Badddays(utcnow('yyyy-mm-dd')%2C15)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20remove%20the%20'adddays'%20function%2C%20it%20works.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F4685%22%20target%3D%22_blank%22%3E%40Merwan%20Hade%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-4737%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Flow%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-225314%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20creating%20DateTime%20Condition%20in%20Microsoft%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-225314%22%20slang%3D%22en-US%22%3E%3CP%3Eutcnow()%20has%20a%20time%20in%20it%20as%20well%20as%20the%20date.%26nbsp%3B%20your%20contract%20end%20date%20likely%20includes%20a%20time%20as%20well%2C%20so%20adding%2020%20days%20will%20still%20result%20in%20a%20false%20unless%20the%20time%20of%20the%20contract%20end%20date%20and%20the%20utcNow()%20current%20time%20happen%20to%20be%20exactly%20the%20same%20(not%20impossible%20but%20very%20unlikely).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Etry%20using%20the%20startofday()%20function%20along%20with%20what%20you%20have.%26nbsp%3B%20that%20should%20keep%20any%20difference%20in%20time%20from%20causing%20a%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-166831%22%20slang%3D%22en-US%22%3ERE%3A%20Issue%20with%20creating%20DateTime%20Condition%20in%20Microsoft%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-166831%22%20slang%3D%22en-US%22%3Ehi%20Mike%20%2F%20Gilbert%20Hi%20Dear%20I%20am%20writing%20the%20following%20to%20a%20SharePoint%20column%20but%20it%20is%20not%20getting%20successful%2C%20the%20condition%20is%20not%20going%20to%20YES.%20%40equals(string(item()%5B'Contract_x0020_End_x0020_Date'%5D)%2C%20adddays(utcNow()%2C%2020))%20if%20you%20can%20guide%2C%20where%20is%20the%20mistake%20thanks%20in%20Advance%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-166748%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20creating%20DateTime%20Condition%20in%20Microsoft%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-166748%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Dear%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20am%20writing%20the%20following%20to%20a%20SharePoint%26nbsp%3Bcolumn%26nbsp%3Bbut%20it%20is%20not%20getting%20successful%2C%20the%20condition%20is%20not%20going%20to%20YES.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%40equals(string(item()%5B'Contract_x0020_End_x0020_Date'%5D)%2C%20adddays(utcNow()%2C%2020))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20you%20can%20guide%2C%20where%20is%20the%20mistake%20thanks%20in%20Advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-148867%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20creating%20DateTime%20Condition%20in%20Microsoft%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-148867%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20am%20using%20the%20SQL%20Server%20Get%20line%20action%20and%20would%20like%20to%20compare%20the%20date%20of%20creation%20of%20a%20line%20in%20a%20table%20(field%20CREDAT_0)%20with%20the%20current%20date%20and%20be%20able%20to%20send%20an%20email%20when%20the%20creation%20date%20is%20today.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20tried%20to%20convert%20the%20utcnow%20to%20the%20exact%20same%20format%20as%20my%20sql%20date%20but%20my%20flow%20keeps%20failing%20(no%20need%20to%20mention%20I%20have%20tried%20it%20without%20the%20condition%20and%20it%20works%2C%20that%20is%20to%20say%20it%20retrieves%20all%20the%20lines%20in%20the%20table).%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3EHere%20is%20the%20sql%20date%20format%26nbsp%3Band%20the%20condition%20I%20have%20put%20in%20place.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20in%20advance%20for%20your%20kind%20help%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F27413iA880D56C13BE51A7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22flow.png%22%20title%3D%22flow.png%22%20%2F%3E%3C%2FSPAN%3EaddDays(utcnow('yyyy-MM-dd%20HHmm%3Ass.fff'%2C0%2C%20'yyyy-MM-dd%20HHmm%3Ass.fff'))%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-4777%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20creating%20DateTime%20Condition%20in%20Microsoft%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-4777%22%20slang%3D%22en-US%22%3EGreat!%20Thanks%20for%20flagging%20it%20as%20the%20correct%20answer.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-4775%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20creating%20DateTime%20Condition%20in%20Microsoft%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-4775%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F2227%22%20target%3D%22_blank%22%3E%40Mike%20Platvoet%3C%2FA%3E.%26nbsp%3B%20That%20worked!!%26nbsp%3B%3C%2FP%3E%3CP%3EI%20converted%20the%20date%20into%20string%20as%20follows%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3Eless(%3CEM%3Estring%3C%2FEM%3E(item()%5B'ContractEndDate'%5D)%2C%20adddays(utcnow()%2C%2015))%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-4769%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20with%20creating%20DateTime%20Condition%20in%20Microsoft%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-4769%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20adddays%20function%20returns%20a%20string%20(iso%20date%20format)%2C%20but%20the%20first%20part%20of%20your%20less%20function%20contains%20a%20date%20format%20that%20has%20not%20been%20formatted%20yet%2C%20so%20I%20suspect%20that%20the%20string%20compare%20does%20not%20give%20a%20proper%20result.%3CBR%20%2F%3EYou%20should%20format%20the%20ContractEndDate%20to%20the%20same%20string%20format%20as%20the%20calculated%20date%20to%20have%20the%20Less%20function%20work%20as%20expected.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

I am trying to create a recurring Flow which checks the difference between a date field [ContractEndDate] and variation from todays date.

 

If ContractEndDate is less than (todays date + 15 days)

 

Looking at the workflow definition language here (https://msdn.microsoft.com/library/azure/mt643789.aspx) i came up with the following logic:

 

@less(item()['ContractEndDate'], adddays(utcnow('yyyy-mm-dd'),15)

 

If I remove the 'adddays' function, it works. 

 

Any ideas?

 

@Merwan Hade

7 Replies
best response confirmed by Gilbert Okello (Frequent Contributor)
Solution

The adddays function returns a string (iso date format), but the first part of your less function contains a date format that has not been formatted yet, so I suspect that the string compare does not give a proper result.
You should format the ContractEndDate to the same string format as the calculated date to have the Less function work as expected.

Thanks @Mike Platvoet.  That worked!! 

I converted the date into string as follows:

less(string(item()['ContractEndDate']), adddays(utcnow(), 15))

Great! Thanks for flagging it as the correct answer.

Hi,

 

I am using the SQL Server Get line action and would like to compare the date of creation of a line in a table (field CREDAT_0) with the current date and be able to send an email when the creation date is today.

I have tried to convert the utcnow to the exact same format as my sql date but my flow keeps failing (no need to mention I have tried it without the condition and it works, that is to say it retrieves all the lines in the table).

Here is the sql date format and the condition I have put in place.

Thanks in advance for your kind help

 

flow.pngaddDays(utcnow('yyyy-MM-dd HHmm:ss.fff',0, 'yyyy-MM-dd HHmm:ss.fff'))"

Hi Dear

 

I am writing the following to a SharePoint column but it is not getting successful, the condition is not going to YES. 

 

@equals(string(item()['Contract_x0020_End_x0020_Date']), adddays(utcNow(), 20))

 

if you can guide, where is the mistake thanks in Advance

hi Mike / Gilbert Hi Dear I am writing the following to a SharePoint column but it is not getting successful, the condition is not going to YES. @equals(string(item()['Contract_x0020_End_x0020_Date']), adddays(utcNow(), 20)) if you can guide, where is the mistake thanks in Advance

utcnow() has a time in it as well as the date.  your contract end date likely includes a time as well, so adding 20 days will still result in a false unless the time of the contract end date and the utcNow() current time happen to be exactly the same (not impossible but very unlikely). 

 

try using the startofday() function along with what you have.  that should keep any difference in time from causing a problem.