Results 1 to 13 of 13

Thread: Dates!

  1. #1
    Join Date
    Dec 2009
    Posts
    9

    Unanswered: Dates!

    before i get to my question i wanted to mention that im not sure how to find out which version, fixpack, etc. of DB2 that im using. i tried using :

    "db2level -> to get db2 version and fixpack level
    db2licm -l -> to get the db2 type (WSE, ESE, etc)"

    but apparently my connection to the server is set to read-only. is there another way?

    onto my question: i am using Access 07 to run a pass thru query to the DB2 machine to return some data. one of the fields is called DATE_ID and is a date field. im am a complete beginner when it comes to DB2 SQL syntax so im unable to manipulate the date fields the way i would like to (month and year to date totals). for example my code for YTD in access is:

    Code:
    Between DateAdd("yyyy",-1,DateSerial(Year(Date()),1,1)) And DateAdd("yyyy",-1,Date())
    and im trying to figure out how to incorporate something similar into the Where clause of my pass through query. i have found DB2 date functions online, but i could not make much sense of them.
    thanks.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    maw230, It all depends on which you learned first. From my DB2-centric point of view, DB2's date functions are clear and simple. It is Access's date functions that make my eyes cross and resort to Google to find what that weird syntax is trying to accomplish.

    If I followed that 'weird' access syntax, you want to get last year's Jan 1 date and the date one year ago from today's date.

    Here is one way to do that (this works natively in DB2 but I am not sure about passed through Access).
    Code:
     some-date BETWEEN DATE(CAST(YEAR(CURRENT_DATE - 1 YEAR) AS CHAR(4) || '-01-01')
                   AND CURRENT_DATE - 1 YEAR
    The last part (after the AND) is fairly simple. CURRENT_DATE is the same as DATE(). It returns today's date. - 1 YEAR subtracts 1 year from that date.

    The more complicated one (after the BETWEEN) is creating a 1 Jan date for the previous year. Starting at the center and working out:

    CURRENT_DATE - 1 YEAR we have already covered. It returns the date 1 year ago from today's date.

    The YEAR( ) function extracts the Year part of the date. In this case that would be 2008. This is an Integer datatype.

    Since I want to concatenate the -01-01 (Jan 1) part fo the date to the Year, the Year has to be converted (or CAST) to a Character datatype.

    CAST ( ... AS CHAR(4)) converts the Integer 2008 to a Character 2008.

    || '-01-01' is concatenated to this character year to produce 2008-01-01.

    Finally the DATE( ) function converts the Character string '2008-01-01' to a DATE datatype so DB2 knows it is a Date.

    This is one way. There are probably others that can accomplish the same task.

    PS I believe you have to run the DB2LEVEL command on the Server to get the version of DB2. If you run it on a Client computer, you will get the version of the DB2 Client.

  3. #3
    Join Date
    Dec 2009
    Posts
    9
    thanks stealth. however i am having a slight problem with your code. i am getting a "token was not valid" error that is pointing to the "||" section of this code portion:
    Code:
     CHAR(4) || '-01-01')
    any thoughts? it could have to do with the pass through from Access but i dont know.

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by maw230 View Post
    thanks stealth. however i am having a slight problem with your code. i am getting a "token was not valid" error that is pointing to the "||" section of this code portion:
    Code:
     CHAR(4) || '-01-01')
    any thoughts? it could have to do with the pass through from Access but i dont know.
    You can change token || with word CONCAT...

    Lenny

  5. #5
    Join Date
    Dec 2009
    Posts
    9
    thanks Lenny but that gave me a "keyword CONCAT not expected" error. here is the SQL as it's written in the pass through query:

    Code:
    SELECT LINE, CASH_UNITS,  CHARGE_UNITS,  CASH_SALES, CHARGE_SALES, CASH_SALES_AT_COST,  CHARGE_SALES_AT_COST
    
    FROM DWQRYDATA.SLSDSI
    
    Where DATE_ID BETWEEN DATE(CAST(YEAR(CURRENT_DATE - 1 YEAR) AS CHAR(4) CONCAT '-01-01')
                   AND CURRENT_DATE - 1 YEAR
    
    group by LINE, CASH_UNITS,  CHARGE_UNITS,  CASH_SALES, CHARGE_SALES, CASH_SALES_AT_COST,  CHARGE_SALES_AT_COST
    
    order by LINE

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    maw230, You are missing a close parenthesis after CHAR(4) (which you got from my typo in my original post):
    Code:
    SELECT LINE, CASH_UNITS,CHARGE_UNITS,  CASH_SALES, CHARGE_SALES, CASH_SALES_AT_COST,  CHARGE_SALES_AT_COST
    FROM DWQRYDATA.SLSDSI
    Where DATE_ID BETWEEN DATE(CAST(YEAR(CURRENT_DATE - 1 YEAR) AS CHAR(4) ) CONCAT '-01-01')
                   AND CURRENT_DATE - 1 YEAR
    group by LINE, CASH_UNITS,  CHARGE_UNITS,  CASH_SALES, CHARGE_SALES, CASH_SALES_AT_COST,  CHARGE_SALES_AT_COST
    order by LINE

  7. #7
    Join Date
    Dec 2009
    Posts
    9
    ha, no worries Stealth. the code works great, but i have to say it will take some practice to learn these db2 date functions.

    so say now i would like to to year to date for the current year. would i just remove the "-1 year" statements??

  8. #8
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    That is correct. For the current year do not subtract 1 year from the CURRENT DATE.

    Here is a link to the DB2 (V9.7) Information Center that lists all the Scalar Functions:

    Scalar functions - IBM DB2 9.7 for Linux, UNIX, and Windows

    Since you are not sure of what version of DB2 you are accessing, some of these may not work. The Date/Time functions are relatively easy to pick out but there may be others you can use.

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Simplification

    Quote Originally Posted by maw230 View Post
    ha, no worries Stealth. the code works great, but i have to say it will take some practice to learn these db2 date functions.

    so say now i would like to to year to date for the current year. would i just remove the "-1 year" statements??
    Following query looks better to me:
    Code:
    SELECT LINE, CASH_UNITS,
    CHARGE_UNITS, CASH_SALES, 
    CHARGE_SALES, CASH_SALES_AT_COST,  
    CHARGE_SALES_AT_COST
    FROM DWQRYDATA.SLSDSI
    Where DATE_ID BETWEEN date(varchar(YEAR(CURRENT_DATE) - 1 ) CONCAT '-01-01') 
                                 AND CURRENT_DATE - 1 YEAR
    group by LINE, CASH_UNITS,  CHARGE_UNITS,  
    CASH_SALES, CHARGE_SALES, 
    CASH_SALES_AT_COST,  
    CHARGE_SALES_AT_COST
    order by LINE
    Lenny

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up Improving

    We can improve the formula and performance too:

    Code:
    SELECT LINE, CASH_UNITS,
    CHARGE_UNITS, CASH_SALES, 
    CHARGE_SALES, CASH_SALES_AT_COST,  
    CHARGE_SALES_AT_COST
    FROM DWQRYDATA.SLSDSI
    Where 
    DATE_ID BETWEEN 
    current date - (dayofyear(current date) - 1)  days - 1 year  
        AND CURRENT_DATE - 1 YEAR
    group by LINE, CASH_UNITS,  CHARGE_UNITS,  
    CASH_SALES, CHARGE_SALES, 
    CASH_SALES_AT_COST,  
    CHARGE_SALES_AT_COST
    order by LINE
    Lenny

  11. #11
    Join Date
    Dec 2009
    Posts
    9

    Been a while

    i know it's been a while, but i have another question about this topic. just want to know what to change to make it Month to date? should be fairly simple, but my efforts haven't payed off quite yet.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see Lenny's code
    current date - (dayofyear(current date) - 1) days - 1 year

    It gets last year's January 1st.
    So, to get this year's January 1st,
    current date - (dayofyear(current date) - 1) days

    And note that "dayofyear(current date)" gives you days from this year's January 1st. to current date.

    If you want to get 1st day of this month,
    see Lenny's code and consider the fact that "DAY(current date)" gives you day part of current date(days from this month's 1st day to current date),
    then you would easily get the answer by changing the Blue part of the following code.

    current date - (dayofyear(current date) - 1) days

  13. #13
    Join Date
    Dec 2009
    Posts
    9
    thank you tonkuma. i wish i were more familiar with DB2. this is what i have come up with for Month to date values for this year and last year:

    Code:
    Where 
    DATE_ID BETWEEN 
    current date - (dayofyear(current date) - 1)  days - 1 year  
        AND CURRENT_DATE - 1 YEAR
        OR BETWEEN current date - (DAY(current date)) - 1) days
        AND  CURRENT_DATE
    but it is not correct. im having a hard time understanding how these date functions work.

Posting Permissions

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