Current Date Help Please...

coffeebreak · #1 (**permalink**) 08-30-06, 23:43

Hi, I have a sql statement as follows: SELECT current date - 1 day FROM sysibm.sysdummy1; This is returning the following: Aug 31, 2006 (The "- 1 day" is so I return yesterdays date - this works fine) All works great - except... I would like the format to be: 31-7-2006 I have tried everything I can - can't FORMAT the date the way I want. Any help on this would be greatly appreciated. Thanks in advance, Brad

Peter.Vanroose · #2 (**permalink**) 08-31-06, 02:15

Code:

char(CURRENT DATE - 1 day, EUR)

will convert that date to the representation "31.08.2006". This is the closest you can get in a simple way.
But you can of course manipulate these 10 characters at will with SUBSTR() and CONCAT().
Or alternatively use the functions DAY(), MONTH() and YEAR(), combined with CAST, e.g.

Code:

CAST(day(current date - 1 day) AS varchar(2)) || '-' ||
CAST(month(current date - 1 day) AS varchar(2)) || '-' ||
CAST(year(current date - 1 day) AS varchar(2))

umayer · #3 (**permalink**) 08-31-06, 02:43

you might also use the TRANSLATE-function:

SELECT
TRANSLATE(CHAR(CURRENT DATE - 1 DAY, EUR),'-','.')
FROM SYSIBM.SYSDUMMY1;

if used in a program, consider using
SET :host = TRANSLATE(CHAR(CURRENT DATE - 1 DAY, EUR),'-','.') ;

instead of
SELECT
TRANSLATE(CHAR(CURRENT DATE - 1 DAY, EUR),'-','.')
INTO :host
FROM SYSIBM.SYSDUMMY1 ;

coffeebreak · #4 (**permalink**) 08-31-06, 18:28

Thanks for your help - works great!

Internet Services

Web Development

Digital Marketing

Consumer Tech