topic Re: Date format in QlikView

Date format

upaliwije — Sat, 22 Mar 2014 06:01:33 GMT

Hi friends

SELECT trunc(to_date(deb_trn_date,'DD-MON-YYYY' ))as TRN_DATE

When I run above part of the sql in script I get the following result

3/3/0014 12:00:00AM

But what i expect is

03-MAR-2014

PLS HELP ME TO CORRECT MY SQL TO OBTAIN DESIRED RESULT

Re: Date format

its_anandrjs — Sat, 22 Mar 2014 06:12:50 GMT

Hi,

Why you doing this in SQL after loading from sql do in another resident table like

Date(TRN_DATE,'DD-MON-YYYY') as TRN_DATE

Hope this helps

Thanks & Regards

Re: Date format

nilesh_gangurde — Sat, 22 Mar 2014 06:27:50 GMT

use the below mentioned script.

Load DATE(Floor(deb_trn_date),'DD-MMM-YYYY') as TRN_DATE;

SELECT deb_trn_date from TABLE_NAME;

-Nilesh

Re: Date format

sunilkumarqv — Sat, 22 Mar 2014 06:31:40 GMT

use these below syntax , I don't understand these many trunc(to_date(deb_trn_date)

CONVERT(VARCHAR, GETDATE(), 106)

Hope these will helpful

Re: Date format

jagan — Sat, 22 Mar 2014 06:37:49 GMT

Hi,

Try like this remove Trunc().

SELECT (to_date(deb_trn_date,'DD-MON-YYYY' ))as TRN_DATE

Or in qlikview try like this

TableName:

LOAD

Date(TRN_DATE, 'DD-MMM-YYYY') AS TRN_DATE;

SELECT trunc(to_date(deb_trn_date,'DD-MON-YYYY' ))as TRN_DATE

Regards,

Jagan.

Re: Date format

maxgro — Sat, 22 Mar 2014 07:34:37 GMT

assuming your database is oracle

if you want to format a date at the oracle side you should use the to_char function

SELECT to_char(deb_trn_date,'DD-MON-YYYY' ) as TRN_DATE

I think is better to keep the original data and, if needed, transform in qlikview

an example of the different ways to format date (oracle side, qlik side)

SCRIPT

load

data_oracle,

data_oracle_char,

date(floor(data_oracle), 'DD-MMM-YYYY') as data_qlik

;

SQL select

DATA as "data_oracle",

to_char(DATA, 'DD-MON-YYYY') as "data_oracle_char"

from radar.logradar;

RESULT

Re: Date format

upaliwije — Sun, 23 Mar 2014 11:24:05 GMT

Thanks all