Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2013
    Posts
    8

    Unanswered: DB2 Version: I-Series 5, Version 6, Release 1, Modification: 0

    Question 1:
    I wrote the following query. In the WHERE clause, HDR.CSDATE and Store.STSDAT are both Date Fields with the following structure: Length = 6, Field Type = Packed. My objective is to query the date range Sunday to Saturday of the previous week. I would like it to have the flexibility to be run on any day of the current week. Can anyone please help me with the syntax?

    SELECT Fields
    FROM MM750LIB.CSHHDR HDR
    left join MM750LIB.TBLSTR Store
    on hdr.csstor = Store.STRNUM

    where
    HDR.CSDATE >= INTEGER( TO_CHAR((CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYs) - 7 DAYs , 'RRMMDD') )
    and HDR.CSDATE <= INTEGER( TO_CHAR((CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYs) - 1 DAYs , 'RRMMDD') )
    and Store.STSDAT <= INTEGER( TO_CHAR((CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYs) - 1 DAYs , 'RRMMDD') )

    [SQL0171] Argument 1 of function TO_CHAR not valid.




    Question 2:
    If dates were stored in date format in db2, as in SQL Server, this would be a little easier. I could then just pass the 2 named parameters @StartDate and @EndDate, which are created in the report, as the following:

    SELECT Fields
    FROM MM750LIB.CSHHDR HDR
    left join MM750LIB.TBLSTR Store
    on hdr.csstor = Store.STRNUM
    WHERE
    HDR.CSDATE >= @StartDate
    And HDR.CSDATE <= @EndDate
    And Store.STSDAT <= @EndDate


    Does db2 allow the passing of named parameters? If yes, how would I write that query?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The are not very many DB2 I-Series people on this forum.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •