Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2013

    Unanswered: Query to get date from a fixed date

    I want to build a query that returns data from 02/01/13 (1st Feb 2013) to current date. Means I want to have CURRENT YTD view as my fiscal period starts from Feb 01 every year.
    I have to make YEAR dynamic as that will be changing every year. I started with the following and that returns me 02/01/13:

    SELECT '02/01/'||substr(char(YEAR (current timestamp)),3,4) from SYSIBM.SYSDUMMY1 ;

    When I put this in WHERE clause in actual query it fails because datatypes are not same anymore, one is string and one is date type:

    SELECT '02/01/'||substr(char(YEAR (current timestamp)),3,4) from SYSIBM.SYSDUMMY1
    and Current Date;

    Request you to suggest me how should I can build my where clause to get dates between 01 Feb of every year and current date. CLAIM_DATE in the above query is Date type.

  2. #2
    Join Date
    Apr 2006
    Provided Answers: 11
    maybe by using date function
    date('2013-02-20') will return date format of character input
    DATE - IBM DB2 9.7 for Linux, UNIX, and Windows
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5-V11 Fundamentals- DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified

  3. #3
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    How about
    current date - dayofyear(current date) days + 32 day
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Feb 2013
    Thanks much. I should have thought this:-)....Thanks again both of you for your reply.

  5. #5
    Join Date
    Jan 2013
    Provided Answers: 1
    Your real problem is that you are writing 1960's COBOL in SQL. They had to use strings back then, but we have DATE data types today. You also do not seem to know that the ONLY display format in ANSI/ISO Standard SQL is ISO-8601 “yyyy-mm-dd” and not some local dialect.

    Build a calendar table with one column for the calendar data and other columns to show whatever your business needs in the way of temporal information, such as fiscal dates. Do not try to calculate holidays in SQL -- Easter alone requires too much math.

    One way to do this is to add a small integer column that holds the fiscal calender ordinal date. Your year to date will simply be the subset between “yyyy-001” and whatever the fiscal ordinal date that matches the calendar date.

    You can get the same effect with some temporal math, but SQL is a data base language. It is not meant for computations or display. That is what presentation layers do in a tiered architecture.

  6. #6
    Join Date
    Feb 2008
    Quote Originally Posted by yale_work View Post
    Thanks much. I should have thought this:-)....Thanks again both of you for your reply.
    I'm curious to know what do you want if CURRENT DATE was in January.

    n_i's expression will return February 01 of same year.
    So, if CURRENT DATE was '2013-01-15', resulting between predicate might be equivalent to
    CLAIM_DATE BETWEEN DATE('2013-02-01') AND DATE('2013-01-15')

Posting Permissions

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