RE:Date format Issue

[Chanty4u]

Hi all,

input date format

column name: Voice_date

Data is in below format for Voice_date

August 31, 2015 9:00:00 PM IST

February 13, 2015 4:30:00 PM IST

September 6, 2015 5:00:00 AM IST

Required format output

YYYYMMDD

Thanks in advance

Best

Chanty

[settu_periasamy · Accepted solution]

Hi,

Try

T1:
LOAD *,Date(Floor(Date#(SubField(VoiceDate,' IST',1),'MMMM DD, YYYY hh:mm:ss TT')),'YYYYMMDD') as Date;

LOAD * INLINE [
  VoiceDate
    "August 31, 2015 9:00:00 PM IST"
    "February 13, 2015 4:30:00 PM IST"
    "September 6, 2015 5:00:00 AM IST"
];

[clondono]

Hi Chanty,

In the object properties select the number tab and check "Override Document Settings".  Select date and enter the Format Pattern you are looking for:

[tamilarasu]

Also try,

Load *,

Date(Date#(Left(Voice_date, Index(Voice_date, ' ', 3)),'MMMM DD, YYYY'),'YYYYDDMM') as Date

From Source;

[Chanty4u]

tq settu.

is it possible to create in Sql?   using   Coalesce Function

by that we can implement in Qvd's.

[Chanty4u]

thnx tamil.

bt  is this posible to get in SQl?

[settu_periasamy]

No Idea how to relate this date format with Coalesce function in Sql. I assume you are talking about 'Replace Null Values'

May be you can look it here..

Replace Null Values

Coalesce statement in Qlikview‌

And from "QlikView for Developers Cookbook"

[Chanty4u]

Not that settu.

chk dis sample example:

coalesce ( (TO_CHAR( Voice_DATE_NEW, 'DY')|| ' - ' || DATE(Voice_DATE_NEW)) ,(TO_CHAR( al_air_date, 'DY')|| ' - ' || date(al_air_date)), (TO_CHAR( L.DATE, 'DY')|| ' - ' || date(AL.DATE))  ) as Voice_DATE,

this expression gives us the output as "WED - 2016-02-17"   but i need  in  YYYYMMDD  

[HirisH_V7]

Hi,

Check this,

Date:
LOAD *,
TimeStamp(Timestamp#(Replace(Voice_date,Right(Voice_date,4),''),'MMMM DD, YYYY hh:mm:ss TT'),'DD/MM/YYYY h:mm:ss[.fff] TT') as TimeStamp,
Date(Timestamp#(Replace(Voice_date,Right(Voice_date,4),''),'MMMM DD, YYYY hh:mm:ss TT'),'YYYYMMDD')as DateField


 INLINE [
    Voice_date
    "August 31, 2015 9:00:00 PM IST"
    "February 13, 2015 4:30:00 PM IST"
    "September 6, 2015 5:00:00 AM IST"
];

Front end:

Hope this Helps,

PFA,

Hirish

[tamilarasu]

Coalesce is similar to Alt function in Qlikview. Try below Sql statement.

coalesce ( DATE_FORMAT(Voice_DATE_NEW,'%Y%m%d') ,DATE_FORMAT(al_air_date,'%Y%m%d'), DATE_FORMAT(AL.DATE,'%Y%m%d')  ) as Voice_DATE,