Date Additions

[Former Member]

I have a Command Query where i select fields between a data range from the table. The query looks like

SELECT field1, field2

FROM Table 1

Where Date Field between {?arg_start_date} and {?arg_stop_date}

I basically wanted to query date range between ARG_STOP_DATE + 1 ( Extra Day)

When i use the following it does not work. Is there a date add function where i can use.

SELECT field1, field2

FROM Table 1

Where Date Field between {?arg_start_date} and {?arg_stop_date} + 1

[Former Member · Accepted solution]

Try this

SELECT field1, field2

FROM Table 1

Where Date Field >= {?arg_start_date} and Date Field <= {?arg_stop_date} + 1

Regards,

Raghavendra

[Former Member · Accepted solution]

I tried it does not work. I expected it to work by adding 1 to the Arg_stop_date. But it gives me an error message. We need to do some thing like a DateAdd function. I am not able to get the Dateadd function work.

[Former Member · Accepted solution]

What is your backend database?

[Former Member · Accepted solution]

My Backend database is UDB DB2. Sorry i must have mentioned it in my earlier post

[patrick_genest · Accepted solution]

Hi Srinivasa,

Each database provider uses different implementation of SQL, so when writting an SQL Query, the best place to find information on the syntax you need to use will be in your sepecifc database documentation or on online forums for the specific database you are having question on.

Once the SQL Query works outside of Crystal Reports, then it will return the expect data in Crystal Reports when creating a report of a command object and using the SQL Query that you created.

Regarding adding a day to a date field, some database have the function "dateadd", but it might not be an available function in DB2. I looked on IBM Support Website, and one article recommend to use the following syntax to add a day:

DateField + 1 DAY  

So in your SQL Query it will look like:

SELECT field1, field2 
FROM Table 1
Where Date Field between {?arg_start_date} and ({?arg_stop_date} + 1 DAY)

Hope this help!

[Former Member]

adding whole numbers to date values should add days, so your formula looks ok to me.

Try creating a formula field, and put your {?arg_stop_date} + 1 statement in it, put it in the headder of your report, run it, and see what you get.

You may also want to check that your params are defined as dates.