Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
scotly-victor
Creator II
Creator II

Date Conversion Issue

Hi All ,

I have two set of date format in my Date field  31/12/2015 18:57 and 1/1/2016 9:30:34 AM

I wanted combine both format into same  format.

I had tried used all conversion but no results .

I have attached screen shot for your reference

1 Solution

Accepted Solutions
arulsettu
Master III
Master III

try this

Load

date(Alt(

  Date#(check,'DD/MM/YYYY hh:mm'),

  Date#(check,'DD/MM/YYYY hh:mm:ss ff'),

  date(check,'DD/MM/YYYY')

 

),'DD/MM/YYYY') as check;

LOAD * INLINE [

check

31/12/2015 18:57

1/1/2016 9:30:34 AM

42370.5875

];

or post sample qvf

View solution in original post

9 Replies
Chanty4u
MVP
MVP

try with alt

Load

Date(Alt(

  Date#(Date,'DD/MM/YYYY hh:mm '),

  Date#(Date,'DD-MM-YYYY')

)) as Date;

LOAD * INLINE [

    Date

     31/12/2015 18:57

1/1/2016 9:30:34 AM

];

scotly-victor
Creator II
Creator II
Author

Sorry, didn't work.

MindaugasBacius
Partner - Specialist III
Partner - Specialist III

The script:

LOAD *

  ,Alt(Timestamp(Timestamp#(Date, 'DD/MM/YYYY hh:mm:ss TT'), 'DD/MM/YYYY hh:mm'),

  Timestamp(Timestamp#(Date, 'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY hh:mm')) as new_Date

INLINE [

    Date

    1/1/2016 9:30:34 AM

    31/12/2015 18:57

];

Results:

Screenshot_1.jpg

arulsettu
Master III
Master III

try this

Load

date(Alt(

  Date#(check,'DD/MM/YYYY hh:mm'),

  Date#(check,'DD/MM/YYYY hh:mm:ss ff')

),'DD/MM/YYYY') as check;

LOAD * INLINE [

check

31/12/2015 18:57

1/1/2016 9:30:34 AM

];

scotly-victor
Creator II
Creator II
Author

I am getting 2015 records

Error.PNG

But 2016 missing

scotly-victor
Creator II
Creator II
Author

Is there any way to convert this into same format

date.PNG

arulsettu
Master III
Master III

try this

Load

date(Alt(

  Date#(check,'DD/MM/YYYY hh:mm'),

  Date#(check,'DD/MM/YYYY hh:mm:ss ff'),

  date(check,'DD/MM/YYYY')

 

),'DD/MM/YYYY') as check;

LOAD * INLINE [

check

31/12/2015 18:57

1/1/2016 9:30:34 AM

42370.5875

];

or post sample qvf

MindaugasBacius
Partner - Specialist III
Partner - Specialist III

Alt(Date(Date, 'DD/MM/YYYY hh:mm')

Timestamp(Timestamp#(Date, 'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY hh:mm')) as new_Date 

brijesh_fofadiy
Contributor III
Contributor III

Hi,

Try this:

Date(floor(Date))