Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2006
    Posts
    2

    Red face Unanswered: DB2 Date Query problem

    Hello,

    I am quite new to SQL and Unix so I am having a problem with what may be thought of as a simple problem.

    I have a table (name=tdb.tb_rf_day) which has a date column using the following format,
    "2006-01-01-00.00.00.000000"

    I would like to query the table to display the output as the day of the week (Example - 2006-01-01-00.00.00.000000 would display as "Sunday")

    Here's the hard part, I have an existing query pulling other information out of the table as well and I would like to do everything in 1 query if possible.

    Query example -
    Code:
    db2 -ec "SELECT TDB.TB_RF_DAY.datetime AS DATE, TDB.TB_RF_DAY.BANK_HOLIDAY AS HOLIDAY, TDB.TB_RF_DAY.DATETIME AS DAY_OF_THE_WEEK, TDB.TB_RF_DAY.SEQUENCE_NO AS SEQUENCE_DATE from TDB.TB_RF_DAY WHERE BATCH_ID=2"
    The second reference (TDB.TB_RF_DAY.DATETIME AS DAY_OF_THE_WEEK) to the DATETIME field is where I would like to have my conversion done.

    Thanks in advance.

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    unable to understand your problem
    anyways there is a function - dayname

    C:\Program Files\IBM\SQLLIB\BIN>db2 values dayname('2006-01-01-00.00.00.000000')

    1
    ------------
    Sunday

    --Rahul

  3. #3
    Join Date
    Jul 2005
    Location
    Irvine, CA
    Posts
    23
    Rahul's solution works because he is converting the timestamp data type into the dayname format. Spanky1968, it appears that you think that the "AS DATE" will do the conversion. This will simply name the column returned as "DATE". This is referred to as aliasing a column name.

    You need to use funtions to convert data as Rahul shows in hist post.

    I suggest you download the DB2 UDB V8 SQL Cookbook by Graeme Birchall. Search for it on Google. The V7.2 or V8.2 are just as good for generic SQL tutorials. This free book will help give you the background to get started.

    -- Steve

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    On z/OS you won't find the Dayname function; in that case use the Dayofweek function (returning a number between 1 and 7) and convert it to text with a CASE expression.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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