I want to add a selection criteria to my SQL. The user will select a 'from' and 'to' date, e.g., 11/1/2015 and 12/31/2015. The date column (jsdate) in the table is Decimal (8,0). When I look at the dates in this column it looks like 20,150,612, 20,150,609, 20,150,611, etc. I'm trying to add a selection critieria like "and cast(jsdate as date format 'mm/dd/yyyy' between 11/1/2015 and 12/31/2015". I'm sure I have the syntax wrong but I don't know how to correct it. Thanks for your help......
You need to cast your INTEGER/DECIMAL data to DATE first:
SELECT CAST(CAST(The_Int_Col AS CHAR(8)) AS DATE FORMAT 'YYYYMMDD') The_Date_Col
FROM (SELECT 20160203 The_Int_Col) pre
WHERE The_Date_Col BETWEEN DATE '2016-02-01' AND DATE '2016-02-04';
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
cast (cast (jsdate as Char (8)) as date format 'yyyymmdd') as Date_Col,
where jfinowngp = 'p1'
and jfinownbr = '99'
and date_col between '06/01/2015' and '01/31/2016'
Carlos...........Thanks for looking at this. My sql is above and I tried to incorporate your suggestions but I obviously missed something becuase I'm getting a "Select Failed.  Invalid date supplied for CS087P.JSDATE. This is a pretty simple query but dates and cast make me crazy. Thanks for your help
Check your data for the column jsdate. It seems you may have INTEGERs that don't correspond to licit DATEs.