Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2012
    Posts
    27

    Unanswered: Keyword AS not expected using TIMESTAMPDIFF

    Hello,

    I am still new to DB2 and have been actively reading many forums to answer my questions before writing a new forum, but I failed to find what I was looking for.

    Basically I am trying to find how many days have elapsed between my date field called 'EXP0DATE' and the current timestamp.

    However, I receive an error message that the word 'As' not expected, but I need to have this value set in a column in a .net gridview.

    What do I need to change?

    TIMESTAMPDIFF(16, char(CURRENT TIMESTAMP - EXP0DATE) As DAYSELAPSED

    Thanks,

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by MrDatabase View Post

    What do I need to change?
    May be count your parentheses?

  3. #3
    Join Date
    Mar 2012
    Posts
    27
    It is always good to have a second pair of eyes.

    Thanks,

    But unfortunately I am getting a new error that is telling me "Argument 1 of function - not valid".

    Does that have something to do with '16' that represents days?

    Thank you again for responding back.

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by MrDatabase View Post
    It is always good to have a second pair of eyes.

    Thanks,

    But unfortunately I am getting a new error that is telling me "Argument 1 of function - not valid".

    Does that have something to do with '16' that represents days?

    Thank you again for responding back.
    Try casting exp0date to a timestamp as in:

    values timestampdiff(16, CHAR(current_timestamp - TIMESTAMP(exp0date)))

    /Lennart

  5. #5
    Join Date
    Mar 2003
    Posts
    280
    But if you are only interested in the difference in days, you might want to aim for something like:

    days(current_date) - days(EXP0DATE)

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    TIMESTAMPDIFF function is accurate only when dealing smaller intervals.
    According to the documentation


    The following assumptions may be used in estimating a difference:

    There are 365 days in a year.
    There are 30 days in a month.
    There are 24 hours in a day.
    There are 60 minutes in an hour.
    There are 60 seconds in a minute.


    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I have posted this article repeatedly.
    But, I think it must be worth to post again.

    DB2 Basics: Fun with Dates and Times
    Summary: Just updated with custom date/time formatting!
    This short article explains how to manipulate dates, times and timestamps using SQL on DB2 Universal Database for Windows, UNIX and Linux platforms.
    My comment at the bottom in the article:
    Excellent work!
    Covered concisely almost everything need to know(about date/time in DB2) for beginners of DB2
    including those peoples who came from other DBMS.
    I hope this article to be updated by latest DB2 version.
    For example: to include updated built-in functions covering "Custom Date/Time Formatting",

  8. #8
    Join Date
    Mar 2012
    Posts
    27
    Thanks everybody for the help, I believe the SQL statement written by lelle12 “DAYS (CURRENT_DATE) – DAYS(EXP0DATE) was the closest to returning a value. However, when I run the query I receive NULL values, and I believe the reason why is that values for my EXP0DATE consist of dates formatted like this – 2/25/99, 3/22/02, 4/01/09 which I think you cannot perform a calculation with CURRENT_DATE that will return a value like this 3/23/2012 that supports the entire year format.

    One of my biggest problems is that I am limited to writing any type of functions that could probably correct some of this due to my only SQL querying tool. The only SQL querying I can perform is within the backend of Visual Studio 2010 using an ODBC connection. If there is a Visual Studio expert who has encountered a similar experience and probably knows a lot more than me could be very helpful right now.

    As far as my solution I just pulled existing dates that were place in three different columns in which I concatenated , “EXP0MO || '/' || EXP0DA || '/' || SUBSTRING(EXP0YR, 2,2) As EXP0DATE” into a VIEW. Now I just query the data from my VIEW. The only problem I see is that I have two columns with different formatting dates. The ‘TODAYDATE’ column which I also created in the VIEW was from this code ‘current_date As TODAYDATE’. Another problem I have is that I can’t add the “|| ‘20’ ||” when concatenating EXP0DATE because I have dates that go back to the 90’s.

    Any other suggestions would really be appreciated??? As I am still stuck???

    Thanks,

  9. #9
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by MrDatabase View Post
    Thanks everybody for the help, I believe the SQL statement written by lelle12 “DAYS (CURRENT_DATE) – DAYS(EXP0DATE) was the closest to returning a value. However, when I run the query I receive NULL values, and I believe the reason why is that values for my EXP0DATE consist of dates formatted like this – 2/25/99, 3/22/02, 4/01/09 which I think you cannot perform a calculation with CURRENT_DATE that will return a value like this 3/23/2012 that supports the entire year format.
    Your are confusing date with a string that looks like a date. Try casting it to a date like:

    DAYS (CURRENT_DATE) – DAYS(date(EXP0YR || '-' || EXP0MO || '-' || EXP0DA))

    FWIW it would have been much better to store a dateinformation as a date instead of storing year, month and day separately. Now there is a possibility that some of your "dates" are invalid, so you might need to clean things up. In this thread:

    comp.databases.ibm-db2 | Google Groups

    Troels Arvin describes a function castlesce that can be of assistance for that purpose.


    /Lennart

  10. #10
    Join Date
    Mar 2012
    Posts
    27
    Thanks again lelle12 for your help.

    This database is not of my design and I agree with you 100% that the best practice is to store date information as a date and not separate like in this case.

    Therefore it is not really my place to impugn somebody else’s work, but they really made it hard to pull queries from this database. I’m sweating bullets here. Ha!

    If you recall in my last post I had concatenated 3 columns into one column named “EXP0DATE”. Within that SQL statement I had to perform this code “SUBSTRING(EXP0YR, 2,2)” because the designer place either a zero or a one in front of the year to represent the decade, here is an example of the field “EXP0YR” which has a value of “099” represents the year 1999, and “105” represents the year 2005. So the trick is now the year column. The code you supplied “DAYS (CURRENT_DATE) – DAYS(date(EXP0YR || '-' || EXP0MO || '-' || EXP0DA))” would probably work in this case, but the EXP0YR is now the problem . Also remember I am limited to working with functions because of the tools I have to work with, but I just don’t know what to do. I am out of ideas…

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by MrDatabase View Post
    the designer place either a zero or a one in front of the year to represent the decade, here is an example of the field “EXP0YR” which has a value of “099” represents the year 1999, and “105” represents the year 2005.
    I think it's supposed to mean "the year since 1900"; then it's a simple arithmetic expression to derive the current year.

  12. #12
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by MrDatabase View Post
    Thanks again lelle12 for your help.

    This database is not of my design and I agree with you 100% that the best practice is to store date information as a date and not separate like in this case.

    Therefore it is not really my place to impugn somebody else’s work, but they really made it hard to pull queries from this database. I’m sweating bullets here. Ha!

    If you recall in my last post I had concatenated 3 columns into one column named “EXP0DATE”. Within that SQL statement I had to perform this code “SUBSTRING(EXP0YR, 2,2)” because the designer place either a zero or a one in front of the year to represent the decade, here is an example of the field “EXP0YR” which has a value of “099” represents the year 1999, and “105” represents the year 2005. So the trick is now the year column. The code you supplied “DAYS (CURRENT_DATE) – DAYS(date(EXP0YR || '-' || EXP0MO || '-' || EXP0DA))” would probably work in this case, but the EXP0YR is now the problem . Also remember I am limited to working with functions because of the tools I have to work with, but I just don’t know what to do. I am out of ideas…
    I see, well something like (untested):

    DAYS (CURRENT_DATE) – DAYS(
    date( case substr(EXP0YR,1,1) when '0' then '19'
    when '1' then '20'
    end || substr(EXP0YR,2,2) || '-' || EXP0MO || '-' || EXP0DA)
    )

    /Lennart

  13. #13
    Join Date
    Mar 2012
    Posts
    27
    Yes... if the value is "001" then it is 1901, and again if it is "099" it represents 1999.

    Yes it is a simple arithmetic expression, but not a simple solution if you want to combine the values into one column, or at least not for me.

    Do you know of a solution to calculate days with the least amount of code?

    You will be the hero of the day if you do!



    All the best,

  14. #14
    Join Date
    Mar 2003
    Posts
    280
    with t(EXP0YR,EXP0MO,EXP0DA) as (
    values ('112','06','23')
    , ('093','07','31')
    )
    select
    date( case substr(EXP0YR,1,1)
    when '0' then '19'
    when '1' then '20'
    end || substr(EXP0YR,2,2) || '-' || EXP0MO || '-' || EXP0DA) from t

    1
    ----------
    06/23/2012
    07/31/1993

    As mentioned before, there is a possibility that you have some invalid dates that might have to be dealt with

    /Lennart

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another two examples using Date calculation of DB2 SQL.

    Note: Both used the assumption that exp0yr is "the year since 1900" which was mentioned by n_i.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     t(exp0yr , exp0mo , exp0da) AS (
    VALUES
      ('112' , '06' , '23')
    , ('093' , '07' , '31')
    )
    SELECT exp0yr , exp0mo , exp0da
         , DATE('1899-12-01')
           + INT(exp0yr)  YEARs
           + INT(exp0mo)  MONTHs
           + (exp0da - 1) DAYs
           AS date_1
         , DATE('0' || exp0yr || '-' || exp0mo || '-' || exp0da)
           + 1900 YEARs
           AS date_2
     FROM  t
    ;
    ------------------------------------------------------------------------------
    
    EXP0YR EXP0MO EXP0DA DATE_1     DATE_2    
    ------ ------ ------ ---------- ----------
    112    06     23     2012-06-23 2012-06-23
    093    07     31     1993-07-31 1993-07-31
    
      2 record(s) selected.

Tags for this Thread

Posting Permissions

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