All Forums Database
shaves 22 posts Joined 04/15
03 Feb 2016
cast date between 2 different data types

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......

CarlosAL 512 posts Joined 04/08
03 Feb 2016

Hi.
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.

The_Date_Col

------------

20160203
HTH.
Cheers.
Carlos. 

shaves 22 posts Joined 04/15
03 Feb 2016

select
cast (cast (jsdate as Char (8)) as date format 'yyyymmdd') as Date_Col,
jsdate,
jsn,
junit,
jmyear,
jmake,
jmodel,
jseries,
jbuyid
from veh_mgmt_as400.cs087p
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.  [2666] Invalid date supplied for CS087P.JSDATE.  This is a pretty simple query but dates and cast make me crazy.  Thanks for your help

CarlosAL 512 posts Joined 04/08
04 Feb 2016

Hi.
Check your data for the column jsdate. It seems you may have INTEGERs that don't correspond to licit DATEs.
HTH.
Cheers.
Carlos.

You must sign in to leave a comment.