ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Using Date Expression

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using Date Expression

    I am reading an old style order header database (OH) that has an entry date (OHENTD) in numeric MMDDYY format. When using embedded SQL I want to convert that date to a date field on the SELECT statement and then condition it in the WHERE clause for a from/to date. I've never done this before and am having trouble finding out how to do it. This is what I have:

    ================================================
    exec sql declare ohcsr cursor for
    select date(timestamp_format(digits(ohentd),'MMDDYY')) as new_date,
    ohord#
    from oh
    where ohotyp in ('SI', 'IO') and
    new_date between :from_date and :to_date;
    ================================================
    It does not like the "new_date" in the WHERE clause. Is that considered a host variable and needs the colon in front? Any help is appreciated!!!

  • #2
    The Where clause in a SQL statement is evaluated before the Select so you don't have access to aliases created in the Select. You would have to duplicate the date(...) in the where clause or use a CTE first and use that for the date part of the where clause.

    The order of the basic clauses is:
    1. The from clause
    2. The where clause
    3. The group by clause
    4. The having clause
    5. The select clause
    6. The order by clause
    Last edited by Scott M; August 17, 2018, 12:41 PM.

    Comment


    • #3
      Thanks Scott. So simple to put the date(...) in the where clause. It's Friday!!!! :-)

      Comment


      • #4
        This is something I go over almost weekly.


        Code:
        exec sql declare ohcsr cursor for 
        select date(timestamp_format(digits(ohentd),'MMDDYY')) as new_date,
        ohord# 
        from oh 
        where ohotyp in ('SI', 'IO') and 
        ohentd between Left(Cast(Right(Replace(Char(:from_date, ISO),'-',''),6) As Integer)*100.0001,6) and Left(Cast(Right(Replace(Char(:to_date, ISO),'-',''),6) As Integer)*100.0001,6);
        You can do whatever you want in output column formatting, but in the where predicate you should never modify any column for comparison.

        Instead, modify the comparison expression.

        Jim




        Comment


        • #5
          Why to convert into a date at all? Why not simply turning the numeric date?
          Code:
          Declare C1 Cursor For
          Select ...
          From YourTable
          Where Right(Digits(DateMDY), 2) concat Left(Digits(DateMDY), 4) between :YYMMDD1 and :YYMMDD2;
          Birgitta

          Comment


          • #6
            Thanks all!!!! Gave me an education!!!!

            Comment


            • #7
              Originally posted by ehillbama57 View Post
              I am reading an old style order header database (OH) that has an entry date (OHENTD) in numeric MMDDYY format. When using embedded SQL I want to convert that date to a date field on the SELECT statement and then condition it in the WHERE clause for a from/to date. I've never done this before and am having trouble finding out how to do it. This is what I have:

              ================================================
              exec sql declare ohcsr cursor for
              select date(timestamp_format(digits(ohentd),'MMDDYY')) as new_date,
              ohord#
              from oh
              where ohotyp in ('SI', 'IO') and
              new_date between :from_date and :to_date;
              ================================================
              It does not like the "new_date" in the WHERE clause. Is that considered a host variable and needs the colon in front? Any help is appreciated!!!
              I typically restructure it using Common Table Expression (CTE)

              Code:
              EXEC SQL 
              WITH F1 AS (
              SELECT DATE(TIMESTAMP_FORMAT(DIGITS(OBENTD),'MMDDYY')) AS NEW_DATE, OHORD#,OHOTYP 
                 FROM OH
              )
              SELECT NEW_DATE,OHORD# FROM
                    F1
              WHERE
                  OHOTYP IN ('SI','IO') And
                  New_Date between :From_Date and :To_Date;

              Comment


              • #8
                Rocky... that was what I was trying to do in the original SQL but really did not know how. I really trying to begin using SQL in every scenario I can. This is another note in my "how to do" document.

                Thanks again!!!!

                Comment

                Working...
                X