title: Format [TIMESTAMP] field value in WHERE clause?
body: <p>I am composing a <a href="http://www.sequel-software.com/products/sequel" rel="nofollow">SEQUEL ViewPoint</a> query joining 2 tables. The first table has the following fields: <code>Action</code>, <code>SSAN</code>, <code>MEMNO</code>, <code>RSCODE</code>, <code>USERID</code>, and <code>TIMESTAMP</code> which is in the format of (Ex. <code>2005-03-11-09.54.18.296000</code>). The other tables is simply used to get the Member <code>NAME</code> by joining the two tables on <code>SSAN</code>.</p>
  
  <p>Why my query starts, the user is presented with a dialog for a date which I have designated as <code>StartDate</code>. The idea is to return all desired fields where <code>table1.TIMESTAMP</code> value is greater than the user selected <code>StartDate</code>.</p>
  
  <p>My current query looks like so:</p>
  
  <pre><code> SELECT         memno.1 EDTCDE(L), name.2, 'SYS1' NAME(SYS), "&amp;&amp;startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
   FROM           library1/table1, library1/table2
   JOIN               SSAN.1=SSAN.2
   WHERE          TIMESTAMP&gt;StartDate AND RSCODE='STP'
   UNION 
   SELECT         memno.1 EDTCDE(L), name.2, 'SYS2' NAME(SYS), "&amp;&amp;startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
   FROM           library2/table1, library2/table1
   JOIN               SSAN.1=SSAN.2
   WHERE          TIMESTAMP&gt;StartDate AND RSCODE='STP'
   ORDER BY   sys ASC, memno ASC
  </code></pre>
  
  <p>Which results in:</p>
  
  <pre><code>Fields TIMESTAMP and STARTDATE in WHERE clause are not compatible.
  Cause. . . . .: You are trying to compare two fields that not compatible. One of the following is true:
  1 -- One field is numeric and the other is not numeric (character or date/time)
  2 -- One field is character and the other is not character (numeric or date/time)
  3 -- One field is double-byte and the other is single byte.
  </code></pre>
  
  <p>This is clearly due to my <code>table1.TIMESTAMP</code> filed being in (Ex. <code>2005-03-11-09.54.18.296000</code>) format and my query value <code>StartDate</code> being in (ex. <code>01/01/14)</code>.</p>
  
  <p>How can I format my <code>TIMESTAMP</code> value in the <code>WHERE</code> clause in order to compare it with my <code>StartDate</code> value? (Or is there a better way to go about this? I'm still pretty green when it comes to SQL and Database QWerying)</p>
  
  <h2><strong>EDIT:</strong></h2>
  
  <p>Using <strong><em>Roopesh</em></strong>' suggestion of  <code>timestamp &gt; cast(StartDate as datetime)</code> results in:</p>
  
  <pre><code> SELECT         memno.1 EDTCDE(L), name.2, 'SYS1' NAME(SYS), "&amp;&amp;startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
   FROM           library1/table1, library1/table2
   JOIN               SSAN.1=SSAN.2
   WHERE          TIMESTAMP&gt;Cast(StartDate as datetime) AND RSCODE='STP'
   UNION 
   SELECT         memno.1 EDTCDE(L), name.2, 'SYS2' NAME(SYS), "&amp;&amp;startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
   FROM           library2/table1, library2/table1
   JOIN               SSAN.1=SSAN.2
   WHERE          TIMESTAMP&gt;Cast(StartDate as datetime) AND RSCODE='STP'
   ORDER BY   sys ASC, memno ASC
  </code></pre>
  
  <p><code>There is an error in the WHERE clause. Parser expected ")". Continue Anyway?</code> [YES selected]</p>
  
  <p>I select date <code>01/01/14</code> which shows in the sql as <code>"01/01/14 NAME(StartDate)</code> and receive:</p>
  
  <pre><code> Identifier 'AS' preceding ' datetime)' is used incorrectly.
      Proper SQL syntax rules have been viloated. The identifier cannot occur where it has been found in the statement. Instad of 'AS', SQL syntax rules allow only: ) ,. If you are using *SEQUEL object authority checking, you cannot use runtime variables in place of the allowed values.
  </code></pre>
  
  <hr>
  
  <p>Using <strong><em>Notulysses</em></strong>' suggestion:</p>
  
  <p>Variable <code>StartDate</code> is of Type <code>Date</code> with a length of <code>10</code>, defaulted to <code>01/01/2014</code>.</p>
  
  <pre><code> SELECT         memno.1 EDTCDE(L), name.2, 'SYS1' NAME(SYS), "&amp;&amp;startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
   FROM           library1/table1, library1/table2
   JOIN               SSAN.1=SSAN.2
   WHERE          CAST(TIMESTAMP as Date)&gt;StartDate AND RSCODE='STP
   UNION 
   SELECT         memno.1 EDTCDE(L), name.2, 'SYS2' NAME(SYS), "&amp;&amp;startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
   FROM           library2/table1, library2/table1
   JOIN               SSAN.1=SSAN.2
   WHERE          CAST(TIMESTAMP as Date)&gt;StartDate AND RSCODE='STP
   ORDER BY   sys ASC, memno ASC
  </code></pre>
  
  <p><code>There is an error in the WHERE clause. Parser expected ")". Continue Anyway?</code> [YES selected]</p>
  
  <p>I select date <code>01/01/14</code> which shows in the sql as <code>"01/01/14 NAME(StartDate)</code> and receive:</p>
  
  <pre><code>Identifier 'AS' preceding ' Date)&gt;Sta' is used incorrectly.
  Proper SQL syntax rules have been viloated. The identifier cannot occur where it has been found in the statement. Instad of 'AS', SQL syntax rules allow only: ) ,. If you are using *SEQUEL object authority checking, you cannot use runtime variables in place of the allowed values.
  </code></pre>
  
  <p>ViewPoint Syntax can be strange compared to regular SQL. Very glad we will be rid of it as a standard tool in our shop before long.......</p>
  
  <p><strong>EDIT2 (SOLUTION):</strong></p>
  
  <p>As set forth by <strong><em>JamesA</em></strong> the trick was to use the <a href="http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=/db2/rbafzscadate.htm" rel="nofollow">DATE</a> function: <code>DATE(TIMESTAMP)</code>.</p>