Using Variable in query of type Datetime

[mayaa]

Hi,

I have created a variable NovSt of type Datetime and assigned it a value from the calendar as 31 October 2017. similarly, have another variable as NovEnd with value 04 December, 2017.

I am using this variable in my datasource query as :
SELECT CASE WHEN EVENT_START_DATE BETWEEN '{NovSt}' AND '{NovEnd}' THEN 'NOVEMBER' END

Formed query:
SELECT CASE WHEN EVENT_START_DATE BETWEEN DATE('10/31/2017 00:00:00 AM') AND DATE('12/04/2017 11:59:59 PM') THEN 'NOVEMBER 2017')

EVENT_START_DATE format is : yyyy-mm-dd hh:mm:ss whereas the Variable value format is : 'mm/dd/yyyy hh:mm:ss'. I need Variables format same as EVENT_START_DATE format to check its value in the range.

How do I change its format?
Please note I tried DATE_FORMAT('{NovDate}', '%Y-%m-%d %T') but it returns NULL as I am using MySql

[Alex K.]

Hello,

Please try to use the ToString() method with the necessary format:
SELECT CASE WHEN EVENT_START_DATE BETWEEN '{NovSt.ToString("NecessaryDateTimeFormat")}' AND '{NovEnd.ToString("NecessaryDateTimeFormat")}' THEN 'NOVEMBER' END

Thank you.

[mayaa]

Hi Aleksey,

The solution worked perfectly! Thanks a lot for your quick response.

Regards,
Mayaa

[Alex K.]

Hello

We are always glad to help you!
Please let us know if you need any additional help.

Thank you.

[mayaa]

Yes I would need your help in another issue which relates to Text formatting. Shall I start new thread or continue here?

Thanks.

[Alex K.]

Hello,

Could you explain your issue in more details?

Thank you.