5 Replies Latest reply: Dec 11, 2012 9:40 AM by Miguel Angel Baeyens de Arce RSS

    mismatch of date formats with SQL date() function and qlikview variable

      Hi everybody,

       

      I am trying to use the where clause in SQL to select data from ODBC. The a.sh_sf_dt field is a timestamp the expression date(a.sh_sf_dt) gives me the DD.MM.YYYY format. I try running the following code and get the attached error message. Any idea how I can solve this??

       

      My goal is to put this in a loop so that I can get the inventory quantities by item at the end of each date. That's why it's important for me to be able to say $(vSnapShot) + i.

       

       

      let vSnapShot = Date#('01.01.2012', DD.MM.YYYY);
      
      Load *, brutStok-standartStok as kMetrajStok;
        SQL 
      
        SELECT   a.sh_ma_kod AS ma_kod, a.sh_ds_kod AS desen, a.sh_vry AS varyant, date(a.sh_sf_dt) as snapDate,
        sum(CASE WHEN a.sh_sf_hk_no < 30 THEN a.sh_qty_mt ELSE -1*a.sh_qty_mt END) AS brutStok, 
        sum(CASE WHEN (a.sh_sf_hk_no < 30 AND a.sh_qty_mt = b.ma_kg_top) THEN a.sh_qty_mt 
        WHEN (a.sh_sf_hk_no >= 30 AND a.sh_qty_mt = b.ma_kg_top) THEN -1*a.sh_qty_mt 
        ELSE 0*a.sh_qty_mt END)as standartStok
              FROM DBA.stkhar AS a 
              LEFT JOIN DBA.mam AS b
              ON a.sh_ma_kod = b.ma_kod
          WHERE date(a.sh_sf_dt) <= $(vSnapShot) 
              GROUP BY   snapDate, a.sh_ma_kod, a.sh_ds_kod, a.sh_vry 
              order by   snapDate, ma_kod, desen, varyant
        ;
      
      

       

      Thanks for helping.

        • Re: mismatch of date formats with SQL date() function and qlikview variable
          Miguel Angel Baeyens de Arce

          Hi,

           

          Try just wraping the variable with single quotes:

           

          WHERE date(a.sh_sf_dt) <= '$(vSnapShot)'
          

           

          Hope that helps.

           

          Miguel

            • Re: mismatch of date formats with SQL date() function and qlikview variable

              Hi Miguel I've already tried that but I get the following error messge:

               

              It looks like even though I am using the date() function in SQL it still stays as a timestamp.

               

               

              SQL##f - SqlState: 07006, ErrorCode: 4294967139, ErrorMsg: [Sybase][ODBC Driver][Adaptive Server Anywhere]Cannot convert 01.01.2012 to a timestamp

              SQL

               

                        SELECT   a.sh_ma_kod AS ma_kod, a.sh_ds_kod AS desen, a.sh_vry AS varyant, date(a.sh_sf_dt) as snapDate,

                                                      sum(CASE WHEN a.sh_sf_hk_no < 30 THEN a.sh_qty_mt ELSE -1*a.sh_qty_mt END) AS brutStok,

                                                      sum(CASE WHEN (a.sh_sf_hk_no < 30 AND a.sh_qty_mt = b.ma_kg_top) THEN a.sh_qty_mt

                                                                           WHEN (a.sh_sf_hk_no >= 30 AND a.sh_qty_mt = b.ma_kg_top) THEN -1*a.sh_qty_mt

                                                                           ELSE 0*a.sh_qty_mt END)as standartStok

                               FROM DBA.stkhar AS a

                               LEFT JOIN DBA.mam AS b

                               ON a.sh_ma_kod = b.ma_kod

                              

                                     WHERE date(a.sh_sf_dt) <= '01.01.2012'

                               GROUP BY   snapDate, a.sh_ma_kod, a.sh_ds_kod, a.sh_vry

                               order by   snapDate, ma_kod, desen, varyant

                • Re: mismatch of date formats with SQL date() function and qlikview variable
                  Miguel Angel Baeyens de Arce

                  Hi,

                   

                  Is the "a.sh_sf_dt" field a datetime field or a timestamp field? If the former, then make sure that the input format  "MM.DD.YYYY" is the one that field expects (instead of i.e.: "MDY" or "MM-DD-YYYY"). If the latter, timestamp data types have nothing to do with actual date or time fields, so you will not be able to use that field for comparing...

                   

                  Hope that helps.

                   

                  Miguel

                    • Re: mismatch of date formats with SQL date() function and qlikview variable

                      Hi Miguel,

                       

                      Thanks for the information. I was able to solve the problem by using the following at the end. Strings are easier to manage than dates .

                       

                       

                      let vSnapShot=Date#('01.01.2012', DD.MM.YYYY);

                      stok:

                       

                                          Load *, brutStok-standartStok as kMetrajStok;

                                          SQL

                                                    SELECT   a.sh_ma_kod AS ma_kod, a.sh_ds_kod AS desen, a.sh_vry AS varyant, date(a.sh_sf_dt) as snapDate,

                                                                                  sum(CASE WHEN a.sh_sf_hk_no < 30 THEN a.sh_qty_mt ELSE -1*a.sh_qty_mt END) AS brutStok,

                                                                                  sum(CASE WHEN (a.sh_sf_hk_no < 30 AND a.sh_qty_mt = b.ma_kg_top) THEN a.sh_qty_mt

                                                                                                       WHEN (a.sh_sf_hk_no >= 30 AND a.sh_qty_mt = b.ma_kg_top) THEN -1*a.sh_qty_mt

                                                                                                       ELSE 0*a.sh_qty_mt END)as standartStok

                                                           FROM DBA.stkhar AS a

                                                           LEFT JOIN DBA.mam AS b

                                                           ON a.sh_ma_kod = b.ma_kod

                       

                                                           WHERE a.sh_yil = 2012 AND substr(a.sh_sf_dt,9,2)||'.'||substr(a.sh_sf_dt,6,2)||'.'||substr(a.sh_sf_dt,1,4) <= '$(vSnapShot)'

                       

                                                           GROUP BY   snapDate, a.sh_ma_kod, a.sh_ds_kod, a.sh_vry

                                                           order by   snapDate, ma_kod, desen, varyant

                                                    ;


                       

                       

                      Now I am trying to put this in a loop and I use the following but I get  an error message saying:

                       

                      Script line error:

                      for i=0

                      Any ideas for this one??

                       

                      let vSnapShot=Date#('01.01.2012', DD.MM.YYYY);

                      for i=0

                                do while $(vSnapShot) <= Date#('05.01.2012', DD.MM.YYYY)/*date(Date#(now()))*/

                       

                                          let vSnapShot =date(Date#('01.01.2012', DD.MM.YYYY)+$(i));

                       

                                          stok:

                                          //sh_sf_hk_kod > 30 stok çıkışı; sh_sf_hk_kod < 30 stok girişi

                                          Load *, brutStok-standartStok as kMetrajStok;

                                          SQL

                                                    SELECT   a.sh_ma_kod AS ma_kod, a.sh_ds_kod AS desen, a.sh_vry AS varyant, date(a.sh_sf_dt) as snapDate,

                                                                                  sum(CASE WHEN a.sh_sf_hk_no < 30 THEN a.sh_qty_mt ELSE -1*a.sh_qty_mt END) AS brutStok,

                                                                                  sum(CASE WHEN (a.sh_sf_hk_no < 30 AND a.sh_qty_mt = b.ma_kg_top) THEN a.sh_qty_mt

                                                                                                       WHEN (a.sh_sf_hk_no >= 30 AND a.sh_qty_mt = b.ma_kg_top) THEN -1*a.sh_qty_mt

                                                                                                       ELSE 0*a.sh_qty_mt END)as standartStok

                                                           FROM DBA.stkhar AS a

                                                           LEFT JOIN DBA.mam AS b

                                                           ON a.sh_ma_kod = b.ma_kod

                                                           //WHERE a.sh_yil = 2012 AND ma_kod = 'ME1050-01' AND a.sh_sf_ay = 8

                                                           WHERE a.sh_yil = 2012 AND substr(a.sh_sf_dt,9,2)||'.'||substr(a.sh_sf_dt,6,2)||'.'||substr(a.sh_sf_dt,1,4) <= '$(vSnapShot)'

                                                                 //WHERE a.sh_yil = 2012 AND left(a.sh_sf_dt,10) <= '$(vSnapShot)'

                                                           GROUP BY   snapDate, a.sh_ma_kod, a.sh_ds_kod, a.sh_vry

                                                           order by   snapDate, ma_kod, desen, varyant

                                                    ;

                       

                                          let i =$(i)+1;

                                loop;


                       

                      Thanks again for all your help.