Re: DB2 SQL confusion

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

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
>