Developer forum

Hi Harald,
it looks you need to use the @Code functionality in your case when adding date filtering condition.
You can check this post with some explanation about it.
@Code syntax is described here(evaluate C# code).
For last month the code will look like: @Code(System.DateTime.Now.AddMonths(-1))
Regards, Dmitrij

Hi Dmitrij,

Thank you for your reply, I tried the @Code statement in my Conditional (DatumZoekopdracht = DateTime field):

Unfortunately it gives me the following error:

"Job failed: Failed to open sqlSourceReader. Reason: String was not recognized as a valid DateTime"

Do you have a suggestion of how to solve this?

Hi Harald,
looks like your code is using not right sql date format when calling .ToString(), can you try with code like this:

myDateTime.ToString("yyyy-MM-dd HH:mm:ss")
Regards, Dmitrij

Hello Dimitri,

I'm not using custom code for the retrieval of the sql data. I use the DynamicWeb SQL provider, which is available in the Data Integration module:

So, if what you're saying is correct that means the DW SQL provider is not working correctly and outputs the wrong date format.

Greetz

Hi Harald,
could you write your code used in the @Code statement in your conditional (DatumZoekopdracht = DateTime field)?
Have you tried to run it with formatting its value to string with the following format: ToString("yyyy-MM-dd HH:mm:ss")?
Regards, Dmitrij

Hi Dmitrij, I'm out of office for the next two weeks. After that I'll try this. Thnx

Hello Dmitriy,

I continued with the problem, what i did was:

- Add the @Code statement to the conditional field:

The @Code I use is:
@Code(System.DateTime.Today.AddDays(-(DateTime.Today.Day-1)).AddMonths(-1).ToString("yyyy-MM-dd HH:mm:ss"))
When I test this code here, it gives me a valid string.

But when I try to run the Data Integration export to CSV I still get the error:
Job failed: Failed to open sqlSourceReader. Reason: String was not recognized as a valid DateTime.

When I enter a string directly, like the @code statement should generate: "2015-10-01 00:00:00". The export works with no problems

Hi Harald,
you are missing one ")" bracket in the end of your C# statement, it should be:
@Code(System.DateTime.Today.AddDays(-(DateTime.Today.Day-1)).AddMonths(-1).ToString("yyyy-MM-dd HH:mm:ss"))
Could you try with that?

Hello Dmitriy,

I checked my code, and in my project the last brace ")" was there. It seems I made a copy/paste error in the forum (sorry for that). I've edited the previous post so that the code example is correct. The problem is still the same.

Greetz

Hi Harald,
what version of Dynamicweb are you using?
Is the database to which you are connecting using the SQL provider Dynamicweb database?
Best regards, Dmitrij

Hi Dmitrij,

We are running Dynamicweb version: 8.6.1.14

In the Dynamicweb database we created a extra table to which we are connecting using the SQL Provider.

Greetz Harald

Hi Dmitrij,

I tested my code in Dynamicweb 8.7.0.1 and there it works without problems. The code I used to get the first day of the previous month was:

@Code(System.DateTime.Today.AddDays(-(DateTime.Today.Day-1)).AddMonths(-1).ToString("yyyy-MM-dd HH:mm:ss"))

Thanks for the support,

Greetings Harald