date format

[marcmc]

select cast('20070620' as date format 'yyyymmdd') + -120;

gives me 20/02/2007

but i need the following format:
20070220

How can I do this?

[joedsilva]

-- Queryman / ODBC version ...
SELECT (('20070620' (DATE, FORMAT 'YYYYMMDD')) - 120 ) (FORMAT 'YYYYMMDD' ) (CHAR(8));

-- Bteq/CLI
SELECT (('20070620' (DATE, FORMAT 'YYYYMMDD')) - 120 ) (FORMAT 'YYYYMMDD' );

[marcmc]

SELECT cast(cast((cast(date_id as date format 'yyyymmdd') + v_Days) as date format 'yyyymmdd') as char(8))
FROM GEt_lu_snapshot;

I got what I wanted using this.
There seems to be a little quirk between SQL Assistant and BTEQ.
For example if you run this in Queryman you get 20/02/2007 whereas if you run the same in BTEQ you get 20070220!!

select cast((cast('20070620' as date format 'yyyymmdd') + -120) as date format 'yyyymmdd');

Thanks.

[joedsilva]

The "Quirk" is because ODBC/JDBC applications are free to ignore the formating phrase, and they do just that and display data using their own formats.

When you cast to CHAR(n), the formating is applied at the TD server end, and the resulting "string" is send to the client. which is why it works when you cast to char(n) in Queryman.

On the other hand CLI based applications like Bteq, retains the format while displaying so you don't have to do gimmicks.

And as usual too many casting and formating comes with extra CPU cycles.