Date Comparison 
Author Message
 Date Comparison
Hi,
I'm trying to compare 2 dates in my sql. The first date (Import_dt) is
defined in my dclgen as DATE. The 2nd date(ws-beg-date) is being passed from
CICS. It is a pic x(10) field.

Select ws-group
  from d001_deal_ctl
  where
    import_date = :ws-beg-date

When executing this sql, I get a -180.

any ideas why this is happening?

Thanks..



Fri, 13 Aug 2004 00:34:29 GMT
 Date Comparison

   Can you cast the ws-beg-date to a DATE datatype?  "... where
import_date = (date) ws-beg-date" or something like that...

--
Larry Menard
IBM Workstation Database (DB2) Performance Team
Defender of Geese and of all things Natural

Quote:
> Hi,
> I'm trying to compare 2 dates in my sql. The first date (Import_dt) is
> defined in my dclgen as DATE. The 2nd date(ws-beg-date) is being
passed from
> CICS. It is a pic x(10) field.

> Select ws-group
>   from d001_deal_ctl
>   where
>     import_date = :ws-beg-date

> When executing this sql, I get a -180.

> any ideas why this is happening?

> Thanks..



Fri, 13 Aug 2004 01:09:18 GMT
 Date Comparison
I'm not sure what you mean. If I make it Date(ws-beg-date), I think the
compiler chokes on that statement because ws-beg-date should be a host
variable.


Quote:
>    Can you cast the ws-beg-date to a DATE datatype?  "... where
> import_date = (date) ws-beg-date" or something like that...

> --
> Larry Menard
> IBM Workstation Database (DB2) Performance Team
> Defender of Geese and of all things Natural


> > Hi,
> > I'm trying to compare 2 dates in my sql. The first date (Import_dt) is
> > defined in my dclgen as DATE. The 2nd date(ws-beg-date) is being
> passed from
> > CICS. It is a pic x(10) field.

> > Select ws-group
> >   from d001_deal_ctl
> >   where
> >     import_date = :ws-beg-date

> > When executing this sql, I get a -180.

> > any ideas why this is happening?

> > Thanks..



Fri, 13 Aug 2004 02:52:11 GMT
 Date Comparison
   Never mind... I was thinking of something else when I said that.

   How about printing the current value of  ws_beg_date?  You haven't
said what platform you're on but you mentioned CICS, so I suspect it's
on 390.  I'm using workstation DB2, and the date format on my system is
strictly "yyyy-mm-dd":

1) Slash as a separator:

D:\>db2 insert into larry (values '1111/12/13'), format yyyy-mm-dd
DB21034E  The command was processed as an SQL statement because it was
not a
valid Command Line Processor command.  During SQL processing it
returned:
SQL0180N  The syntax of the string representation of a datetime value is
incorrect.  SQLSTATE=22007

2) Dash as separator, format yyyy-mm-dd

D:\>db2 insert into larry (values '1111-12-13')
DB20000I  The SQL command completed successfully.

3) Dash as separator, format yyyy-dd-mm

D:\>db2 insert into larry (values '1111-13-12')
DB21034E  The command was processed as an SQL statement because it was
not a
valid Command Line Processor command.  During SQL processing it
returned:
SQL0181N  The string representation of a datetime value is out of range.
SQLSTATE=22007

--
Larry Menard
IBM Workstation Database (DB2) Performance Team
Defender of Geese and of all things Natural

Quote:
> I'm not sure what you mean. If I make it Date(ws-beg-date), I think
the
> compiler chokes on that statement because ws-beg-date should be a host
> variable.



> >    Can you cast the ws-beg-date to a DATE datatype?  "... where
> > import_date = (date) ws-beg-date" or something like that...

> > --
> > Larry Menard
> > IBM Workstation Database (DB2) Performance Team
> > Defender of Geese and of all things Natural


> > > Hi,
> > > I'm trying to compare 2 dates in my sql. The first date
(Import_dt) is
> > > defined in my dclgen as DATE. The 2nd date(ws-beg-date) is being
> > passed from
> > > CICS. It is a pic x(10) field.

> > > Select ws-group
> > >   from d001_deal_ctl
> > >   where
> > >     import_date = :ws-beg-date

> > > When executing this sql, I get a -180.

> > > any ideas why this is happening?

> > > Thanks..



Fri, 13 Aug 2004 03:59:50 GMT
 Date Comparison
-Don't forget that host variables are prefixed by :

-In fact, there are also cics ts/tx series that can be run on nt, unix, ...
Of course mvs/os-390/z-os comes first to my mind too.

-As larry said, i'd display the date/01 level to see if it's properly
formatted.
I don't remember what the exec cics accept date output looks like. (format)
You may need to change the date from dd-mm-yy to yyyy-mm-dd or
something similar. (180 means a wrong format)
move a-yy to b-yy and so on.
Check with spufi or somethig else what is the output of
values current date.  It could be the default.  See also your prep/bind
options
to see wether you set the datetime option in there.

The db2 format and the host var format should be the same to be able to
compare them.
Date and char strings are compatible. (as long as the format is good, ...)
(for details, see the sql ref, assignment and comparison)

PM



Fri, 13 Aug 2004 01:51:37 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. SQL & Date comparisons

2. Date comparison on SQL Server after 4 billion milliseconds

3. Date Comparison

4. Date comparison w/SQL Server 6.5

5. Date Comparison

6. Date comparison

7. Return Date Comparison as Boolean Column?

8. Date comparison in JOIN

9. Need help with date comparison.

10. Date comparison

11. Date Comparison. Very Urgent

12. Date Comparison


 
Powered by phpBB® Forum Software