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

    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 VENDR_NBR , CLAIM_AMT
    FROM DB2DBA.TRADE_ALLOW_CLAIM
    WHERE
    CLAIM_DATE BETWEEN (
    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
    Location
    Belgium
    Posts
    2,514
    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 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

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

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

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    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
    Location
    Japan
    Posts
    3,483
    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
  •