Thread: Date add, query

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    42
    Rep Power
    2

    Date add, query


    Hi, I am quite new to sql, and im trying to get this to work..
    Any suggestions how to solve ut? It says that it can't find the column i've created called ISSUEDATE..


    Code:
    select PQ.BondHistory.*, PQ.CLIENT_DATA.I_REF_N, PQ.CLIENT_DATA.I_ISIN_STR, PQ.CLIENT_DATA.I_ISSUEDATE_DT, PQ.CLIENT_DATA.I_MATURITY_DT, dateadd(day,3,PQ.CLIENT_DATA.I_ISSUEDATE_DT) as ISSUEDATE
    from PQ.Bondhistory
    join PQ.CLIENT_DATA
    on PQ.BondHistory.I_ISIN_STR=PQ.CLIENT_DATA.I_ISIN_STR
    where PQ.BondHistory.I_HIST_FIX_DT=ISSUEDATE
    order by PQ.CLIENT_DATA.I_ISSUEDATE_DT desc
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    10
    Rep Power
    0

    Date_Add syntax


    DATEADD() is a MS T-SQL function. You correctly applied the syntax of DATEADD(<date_type>, <number>, <input_date>)

    So unless you accidentally posted this in the wrong forum you want the MySQL function and syntax.

    DATE_ADD(<input_date>, INTERVAL <number> <date_type>)

    That is with only the one comma before the word INTERVAL.

    Your corrected query should read:

    select
    PQ.BondHistory.*,
    PQ.CLIENT_DATA.I_REF_N,
    PQ.CLIENT_DATA.I_ISIN_STR,
    PQ.CLIENT_DATA.I_ISSUEDATE_DT,
    PQ.CLIENT_DATA.I_MATURITY_DT,
    date_add(PQ.CLIENT_DATA.I_ISSUEDATE_DT, INTERVAL 3 DAY) as ISSUEDATE
    from
    PQ.Bondhistory
    join
    PQ.CLIENT_DATA ON PQ.BondHistory.I_ISIN_STR = PQ.CLIENT_DATA.I_ISIN_STR
    where
    PQ.BondHistory.I_HIST_FIX_DT = ISSUEDATE
    order by PQ.CLIENT_DATA.I_ISSUEDATE_DT desc
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    42
    Rep Power
    2
    Solved!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,208
    Rep Power
    4279
    Originally Posted by ChristopherL
    Solved!
    no, not really

    if you assign issuedate as a column alias in the SELECT clause, you ~cannot~ use that alias in the WHERE clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    42
    Rep Power
    2
    Originally Posted by r937
    no, not really

    if you assign issuedate as a column alias in the SELECT clause, you ~cannot~ use that alias in the WHERE clause
    Code:
    select PQ.BondHistory.*, PQ.CLIENT_DATA.I_REF_N, PQ.CLIENT_DATA.I_ISIN_STR, PQ.CLIENT_DATA.I_ISSUEDATE_DT, PQ.CLIENT_DATA.I_MATURITY_DT, dateadd(day,3,PQ.CLIENT_DATA.I_ISSUEDATE_DT) as ISSUEDATE
    from PQ.Bondhistory
    join PQ.CLIENT_DATA
    on PQ.BondHistory.I_ISIN_STR=PQ.CLIENT_DATA.I_ISIN_STR
    where PQ.BondHistory.I_HIST_FIX_DT=dateadd(day,3,PQ.CLIENT_DATA.I_ISSUEDATE_DT)
    order by PQ.CLIENT_DATA.I_ISSUEDATE_DT desc
    That's how it was solved

IMN logo majestic logo threadwatch logo seochat tools logo