CODEWITHSUNDEEP
sqldatabasedb2
Ludwig
Ludwig

Reputation: 832

Displaying Current date instead of yesterday's date in DB2

I created a variable using the following definition.

create variable ABCDATE varchar(26) default((select (current_date - 1 day) from SYSIBM.SYSDUMMY1))

When i displayed the variable ABCDATE using the schema name(SchemaName.ABCDATE), it displayed current date. ideally it should return yesterday's date. In what all situatons, it happens like this?

Upvotes: 1

Views: 3096

Answers (1)

Charles
Charles

Reputation: 23793

Why would you create a variable containing a date as a VARCHAR?

This works for me

create variable wiltc.abcdate date default(current_date - 1 day)

Displaying

select wiltc.abcdate from sysibm.sysdummy1

Note that if you expect the value to change automatically, it won't. The value is set when the varibale is created and wouldn't be updated again.

You'd be better served by having a view

create view yesterday 
  as (select current_date - 1 day as thedate
      from sysibm.sysdummy1
     )

Upvotes: 2

Related Questions