All Forums UDA
marcmc 112 posts Joined 12/05
24 Jul 2007
date format

select cast('20070620' as date format 'yyyymmdd') + -120;gives me 20/02/2007but i need the following format:20070220How can I do this?

joedsilva 505 posts Joined 07/05
24 Jul 2007

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

marcmc 112 posts Joined 12/05
24 Jul 2007

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 505 posts Joined 07/05
24 Jul 2007

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.

You must sign in to leave a comment.