Re: DB2 SQL confusion

Darrell

Is this in the context of stored procedures? I've barely looked at SQL SPs, but I believe you can declare variables there? Then the VALUES statement is great for this - build up some kind of expression that results in what you need, ba-da-bing! Getting dates into numeric variables usually involves conversion to character, then to numeric - nothing direct unless you write some other stored procedure, maybe in RPGLE.

HTH
Vern

rob@xxxxxxxxx wrote:

Charles explained sysdummy1 pretty good. Often used in STRSQL to test certain functions like
select current_date from sysibm/sysdummy1

Now, it's been deprecated by the VALUES statement.
VALUES CURRENT_DATE
....+....1
VALUES 2009-05-12
You can even do a VALUES CURRENT_DATE INTO :HostVar

Which may make more sense than a dummy read of a file (like sysibm/sysdummy1) to get a value.

See also:
values date(current date)
values date('2009-05-12')
values date('2009-02-31')
VALUES ++++++++++
values month(date('2009-05-12'))
Or last day of previous month
values day( date(substr(char(date(current date)),1,8) concat '01')
- 1 day)
or

values case dayofweek( date(substr(char(date(current date)),1,8) concat '01'
- 1 day) when 1 then 'Sunday' when 2 then 'Monday' when 3 then 'Tuesday' when 4 then 'Wednesday' when 5 then 'Thursday' when 6 then 'Friday' when 7 then 'Saturday' End Will give you 'Thursday'.

Rob Berendt