9 Replies Latest reply: Aug 26, 2011 9:13 AM by Jason Newman RSS

    Loading a character field as a date from an SQL database

      Hi,

       

      I have looked a many forum solutions, but I have been unable to resolve my problem so I have bitten the bullet and posted a question!

       

      I have three fields in my SQL database:

      YEAROFASSESS - char(4)

      MONTHOFASSESS - char(2)

      DAYOFASSESS - char(2)

       

      When I load these my script reads:

      ....

      DAYOFASSESS + MONTHOFASSESS + YEAROFASSESS as DateOfAssessment, etc

       

      I want to use DateOfAssessment as the date field for the Calendar so I have tried converting it to a date:

       

      date(DateOfAssessment) as ControlDate,

       

      OR

       

      date(date#(DateOfAssessment('DD/MM/YYY')) as ControlDate

       

      (I even tried changing the definition of the DateOfAssessment to DAYOFASSESS + / + MONTHOFASSESS + / + YEAROFASSESS...)

       

      Unfortunately none of this worked and I get 'date (or date#) is not a recognised function name' error message.

       

      To try and understand what was happening I tried importing dates in text format(26082011) from an excel file and attempting to using the date() function to convert the values. It did not work , and I got an error message stating that I had the wrong number of arguments for the function.

       

      This at least told me the date() function is valid, but maybe not for SQL via an OLEDB connection?

       

      Can someone please explain what I am doing wrong and come up with a possible solution to convert character fields into a date format?

       

       

      Many thanks in advance

       

      Jason

        • Re: Loading a character field as a date from an SQL database
          Miguel Angel Baeyens de Arce

          Jason,

           

          At a first glance, this line is getting trouble

           

          DAYOFASSESS + MONTHOFASSESS + YEAROFASSESS as DateOfAssessment
          

           

          Concatenation of literal strings in QlikView is done with the "&" instead

           

          DAYOFASSESS & MONTHOFASSESS & YEAROFASSESS as DateOfAssessment
          

           

          And if those values are numbers (although they are typed as char in the database, you can use the following

           

          MakeDate(YEAROFASSESS, MONTHOFASSESS, DAYOFASSESS) AS Date
          

           

          And this should work, returning a Date in QlikView format.

           

          Hope that helps.

           

          Miguel Angel Baeyens

          BI Consultant

          Comex Grupo Ibérica

          • Loading a character field as a date from an SQL database
            Kaushik Solanki

            Hi,

             

                Consider that the date in text format coming from your datasource is in DDMMYYYY format from field name "DateOfAssessment".

             

                For example 12082011,13082011,14082011,15082011,16082011,.......

             

              

                So to convert this into date you need to do this.

             

                Load,

                date(date#(

            DateOfAssessment,'DDMMYYYY'),'DD/MM/YYYY') as new_DateOfAssessment

             

                From xyz.

             

                Let me explain what does this expression mean.

             

                here the date#() takes the two parameter, first is the text and second is the format in which you have date in your datasource (i.e 'DDMMYYYY' ), Then that is passed as one of the parameter of date function and the other parameter (i.e 'DD/MM/YYYY') is the format in which you want to conver.

             

             

                Hope you understood.

             

            Regards,

            Kaushik Solanki

            • Loading a character field as a date from an SQL database
              Shubhasheesh Chakraverty

              Hi,

              What Miguelsaid is correct.

              This is special with Excel as in Excel dates are not stored exactly as we see them in cells.

               

              Say you have date like 20110826, then use makedate ( left ( date, 4 ),  mid ( date, 5, 2 ), right ( date, 2 ) );

               

              HTH