cast date between 2 different data types

Database
Enthusiast

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

3 REPLIES
Junior Supporter

Re: cast date between 2 different data types

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. 

Enthusiast

Re: cast date between 2 different data types

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

Junior Supporter

Re: cast date between 2 different data types

Hi.

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

HTH.

Cheers.

Carlos.