Date format

[upaliwije]

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

[its_anandrjs]

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

[nilesh_gangurde]

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

[sunilkumarqv]

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

CONVERT(VARCHAR, GETDATE(), 106)

Hope these will helpful

[jagan]

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.

[maxgro · Accepted solution]

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

[upaliwije]

Thanks all