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: 
anuradhaa
Partner - Creator II
Partner - Creator II

Date Format Problems

Hi,

i'm new to Qlikview and i need to convert 'YYMMDD' date,( this is in as varchar in the database) to 'YYYY-MM-DD' format in Qlikview.

I have tried to_date function but it doesn't work.

Please help me to solve this Problem

1 Solution

Accepted Solutions
deepakk
Partner - Specialist III
Partner - Specialist III

HI Anuradha,

A small query. Since your data is in YYMMDD format eg 100202 for 2nd Feb 2010. but how will the system understand

10(YY) stands for 2010.

Do you have data only from 2000 ???? if yes the you can try out the below code.

Date(makedate('20'&left(SHIP_DATE,2),mid(SHIP_DATE,3,2), right(SHIP_DATE,2)),'YYYY-MM-DD') as SHIP_DATE

Deepak


View solution in original post

12 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     try with this

     Date(Date#(DateField,'YYMMDD'),'YYYY-MM-DD') As Date

Celambarasan

anuradhaa
Partner - Creator II
Partner - Creator II
Author

Thanks For your Reply,

But it doesn't works.

This is the part of the Script i have used

LOAD DESTINATION_CITY,

         ORIGIN_CITY,

         SHIP_DATE,

         Date(Date#("SHIP_DATE",'YYMMDD'),'YYYY-MM-DD')) As "SHIP_DATE"

here it gives the error "Syntax error, missing/misplaced "

Thanks

jagan
Luminary Alumni
Luminary Alumni

Hi,

Remove quotes for SHIP DATE in Date#().

LOAD DESTINATION_CITY,

         ORIGIN_CITY,

         SHIP_DATE,

         Date(Date#(SHIP_DATE,'YYMMDD'),'YYYY-MM-DD')) As SHIP_DATE

Remove quotes for SHIP DATE in Date#().

Hope this helps you.

Regards,

Jagan.

anuradhaa
Partner - Creator II
Partner - Creator II
Author

Still no luck,

this is the complete script i'm trying. i'm tring to load data from QVD file and that file only has the shipdate in varchar format.

aaa:

LOAD DESTINATION_CITY,

         ORIGIN_CITY,

          SHIP_DATE,

Date(Date#(SHIP_DATE,'YYMMDD'),'YYYY-MM-DD'))  As "SHIP_DATE" - //I tried both "SHIP_DATE" n SHIP_DATE

  FROM

meni1.qvd (qvd) ;

Thanks

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

Try with this without including Ship_Date twice.

aaa:

LOAD DESTINATION_CITY,

         ORIGIN_CITY,

          Date(Date#(SHIP_DATE,'YYMMDD'),'YYYY-MM-DD'))  As SHIP_DATE

  FROM

meni1.qvd (qvd) ;

Celambarasan

anuradhaa
Partner - Creator II
Partner - Creator II
Author

Not works.

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

    Using Double closed paranthesis causing a problem in the date function.

aaa:

LOAD DESTINATION_CITY,

         ORIGIN_CITY,

          Date(Date#(SHIP_DATE,'YYMMDD'),'YYYY-MM-DD')  As SHIP_DATE

  FROM

meni1.qvd (qvd) ;

Sorry its my typing mistake.

Check with this now.

Celambarasan

jagan
Luminary Alumni
Luminary Alumni

Hi,

Can you try this, earlier it has an extra bracket

LOAD

     DESTINATION_CITY,

     ORIGIN_CITY,

     SHIP_DATE,

     Date(Date#(SHIP_DATE,'YYMMDD'), 'YYYY-MM-DD')  As [SHIPDATE]    

  FROM

meni1.qvd (qvd) ;

Hope this helps you.

Regards,

Jagan.

anuradhaa
Partner - Creator II
Partner - Creator II
Author

Great.

Thanks Celambarasan

It works but date is invalid.

my shipdate in the database = 110516 (varchar) and i need to convert it to '2011-05-16' in date format.

Thanks