Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Champion III
Champion III

RE:Date format Issue

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

1 Solution

Accepted Solutions
Highlighted

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"

];

Capture.JPG

View solution in original post

13 Replies
Highlighted

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"

];

Capture.JPG

View solution in original post

Highlighted
Creator III
Creator III

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:

1.png

2.png

Highlighted

Also try,

Load *,

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

From Source;

Highlighted
Champion III
Champion III

tq settu.

is it possible to create in Sql?   using   Coalesce Function

by that we can implement in Qvd's.

Highlighted
Champion III
Champion III

thnx tamil.

bt  is this posible to get in SQl?

Highlighted

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"

Capture.JPG

Highlighted
Champion III
Champion III

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  

Highlighted
Master
Master

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:

Date Format IST GMT-211532.PNG

Hope this Helps,

PFA,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
Highlighted

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,