Skip to Content
avatar image
Former Member

Date Additions

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 17, 2008 at 04:05 PM

    Try this

    SELECT field1, field2

    FROM Table 1

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

    Regards,

    Raghavendra

    Add comment
    10|10000 characters needed characters exceeded

    • 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!

  • avatar image
    Former Member
    Sep 17, 2008 at 04:45 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded