12 Replies Latest reply: Mar 1, 2012 7:47 AM by Anuradha Abeysuriya

Date Format Problems

Anuradha Abeysuriya Mar 1, 2012 6:34 AM

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

Re: Date Format Problems

Celambarasan Adhimulam Mar 1, 2012 7:26 AM (in response to Anuradha Abeysuriya )

Hi,
try with this
Date(Date#(DateField,'YYMMDD'),'YYYY-MM-DD') As Date

Celambarasan
Like Show 1 Like (1)

Actions
- Date Format Problems
  
  Anuradha Abeysuriya Mar 1, 2012 6:54 AM (in response to Celambarasan Adhimulam )
  
  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
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Date Format Problems
    
    jagan mohan rao appala Mar 1, 2012 7:01 AM (in response to Anuradha Abeysuriya )
    
    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.
    
    Like Show 0 Likes (0)
    
    Actions
    - Date Format Problems
      
      Anuradha Abeysuriya Mar 1, 2012 7:07 AM (in response to jagan mohan rao appala )
      
      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
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: Date Format Problems
        
        Celambarasan Adhimulam Mar 1, 2012 7:11 AM (in response to Anuradha Abeysuriya )
        
        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
        
        Like Show 0 Likes (0)
        
        Actions
        
        Date Format Problems
        
        Anuradha Abeysuriya Mar 1, 2012 7:15 AM (in response to Celambarasan Adhimulam )
        
        Not works.
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Date Format Problems
        
        Celambarasan Adhimulam Mar 1, 2012 7:27 AM (in response to Anuradha Abeysuriya )
        
        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
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Date Format Problems
        
        Anuradha Abeysuriya Mar 1, 2012 7:30 AM (in response to Celambarasan Adhimulam )
        
        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
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Date Format Problems
        
        Celambarasan Adhimulam Mar 1, 2012 7:33 AM (in response to Anuradha Abeysuriya )
        
        Hi,
             Yes it will give you that date format only.Which format it returns now?
        
        Celambarasan
        
        Like Show 0 Likes (0)
        
        Actions
        
        Date Format Problems
        
        jagan mohan rao appala Mar 1, 2012 7:29 AM (in response to Anuradha Abeysuriya )
        
        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.
        
        Like Show 0 Likes (0)
        
        Actions
        
        Date Format Problems
        
        Deepak Kurup Mar 1, 2012 7:33 AM (in response to Anuradha Abeysuriya )
        
        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
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Date Format Problems
        
        Anuradha Abeysuriya Mar 1, 2012 7:47 AM (in response to Deepak Kurup )
        
        Thanks Deepak,
        
        I have data only from 2000 and you answer helps me lot
        
        Thanks
        
        Like Show 0 Likes (0)
        
        Actions

Go to original post