RE: DATE Datatype in SQL View

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Let's say you have your alpha date in the following format:

02-25-2007

and your current job is set to *MDY with date separator being '/', something
like this would work:

CREATE VIEW mylib/myview AS 
  (SELECT DATE(REPLACE(myfield,'-','/')) AS myDateField FROM mylib/myfile)

or if you don't have access to REPLACE function on the version of OS you're
running:

CREATE VIEW mylib/myview AS
  (SELECT DATE(SUBSTR(myfield,1,2) || '/' || SUBSTR(myfield,4,2) || '/'
SUBSTR(myfield,7,4)) AS myDateField FROM mylib/myfile)

That aside, perhaps your alpha format resembles *ISO (YYYY-MM-DD). In that
case, simply casting to DATE may work, i.e.:

CREATE VIW mylib/myview AS
  (SELECT DATE(myfield) AS myDateField FROM mylib/myfile)

HTH

Elvis

Celebrating 10-Years of SQL Performance Excellence

-----Original Message-----
From: midrange-l-bounces+ebudimlic=centerfieldtechnology.com@xxxxxxxxxxxx
[mailto:midrange-l-bounces+ebudimlic=centerfieldtechnology.com@xxxxxxxxxxxx]
On Behalf Of Ron Adams
Sent: Sunday, February 25, 2007 12:07 AM
To: Midrange Systems Technical Discussion
Subject: Re: DATE Datatype in SQL View

Currently, it is Alpha 10.

Funny, I was just looking thru some Centerfield's newsletters and the
examples you had in there.



On 2/24/07, Elvis Budimlic <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> What's your field's current format?
>
> Celebrating 10-Years of SQL Performance Excellence
>
>
> --
> Ron Adams