Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Posts
    8

    Unanswered: Date where clause assistance needed

    I should start off by saying that I am a SQL Server guy, DB2 format is giving me a run for my $$.

    I need to retreive records with the following date condition (suedo code below):

    SELECT * FROM tablename WHERE
    (issue_ts >= 1/1/current year AND< 9/1/current year)
    AND
    (
    (expires_ts is null AND revoked_ts is null AND disabled_ts is null)
    OR
    (expires_ts > issue_ts + 1 day AND revoked_ts > issue_ts + 1 day AND disabled_ts > issue_ts + 1 day)
    )

    Any help would be much appreciated!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    as a quick outline, follow these steps...

    first, take DAYOFYEAR(CURRENT DATE) and subtract 1 from it, then subtract that number of days from the current date
    Code:
    CURRENT DATE - ( DAYOFYEAR(CURRENT DATE) - 1 ) DAY
    this gives you january 1st of the current year

    then just add 9 months to that for september 1st

    and there you have your date range boundaries

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2010
    Posts
    8

    Good start!

    Perfect, this gets me started.....

    select
    CURRENT DATE - ( DAYOFYEAR(CURRENT DATE) - 1 ) DAY AS JAN,
    CURRENT DATE - ( DAYOFYEAR(CURRENT DATE) - 1 ) DAY + 8 MONTHS AS SEPT
    FROM sysibm.sysdummy1

  4. #4
    Join Date
    Oct 2010
    Posts
    8

    Error when using in Where clause

    Why does

    where DATE(SUB2.ISSUED_ON_TS) >= CURRENT DATE - ( DAYOFYEAR(CURRENT DATE) - 1 ) DAY
    AND DATE(SUB2.ISSUED_ON_TS) < CURRENT DATE - ( DAYOFYEAR(CURRENT DATE) - 1 ) + 8 MONTHS

    give me the following error?

    "SQLCODE = -171, ERROR: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 2 OF - IS INVALID"


    Yet,

    select
    CURRENT DATE - ( DAYOFYEAR(CURRENT DATE) - 1 ) DAY AS JAN,
    CURRENT DATE - ( DAYOFYEAR(CURRENT DATE) - 1 ) DAY + 8 MONTHS AS SEPT
    FROM sysibm.sysdummy1

    works fine....?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    quite right, it's 8 months from jan 1 to sept 1

    are you sure you can say + 8 MONTHS?

    shouldn't it be + 8 MONTH?

    in any case, i think you have to repeat the DAY...
    Code:
    AND DATE(SUB2.ISSUED_ON_TS) 
      < CURRENT DATE - ( DAYOFYEAR(CURRENT DATE) - 1 ) DAY 
                                            + 8 MONTHS
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2010
    Posts
    8

    active Elmeafty

    Quote Originally Posted by r937 View Post
    quite right, it's 8 months from jan 1 to sept 1

    are you sure you can say + 8 MONTHS?

    shouldn't it be + 8 MONTH?

    in any case, i think you have to repeat the DAY...
    Code:
    AND DATE(SUB2.ISSUED_ON_TS) 
      < CURRENT DATE - ( DAYOFYEAR(CURRENT DATE) - 1 ) DAY 
                                            + 8 MONTHS

    Damn typos...., adding DAY fixed the error. As for MONTH vs MONTHS... I have no idea, both produce the same results. I will use MONTH unless someone says otherwise. Thanks very much for your help!

Posting Permissions

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