8 Replies Latest reply: Nov 1, 2012 9:54 AM by Anita Fuchten RSS

    Timestamp in load script, the time part is not what is should be.

    Anita Fuchten

      I have a variable in the loading script containing a timestamp (vLastLoadDateTime).

      I create a numeric for this, to use in the LOAD statement (vLastLoadDateTimeNUM).

       

      When using it in the LOAD statement the time part of the timestamp is changed to 00:00:00 strange enough in which I do not get the correct results.

       

      See part of the script below:

       

      ...
      LET vLastLoadDateTime = timestamp(vStartLOAD); // voorgaande LOAD start!!
      LET vLastLoadDateTimeNUM = num(vLastLoadDateTime); // voorgaande LOAD start!!
      
      ...
      
      // WHERE CLAUSE
      Serviceorders:
      LOAD *
                ,timestamp([Serviceorders.Orderdatum]          +  [Serviceorders.Ordertijd]) as [Serviceorders.Orderdatumtijd]
                ,timestamp([Serviceorders.Einddatum]          +  [Serviceorders.Eindtijd]) as [Serviceorders.Einddatumtijd] 
                ,timestamp($(vLastLoadDateTimeNUM))as TEST
      //          ,num($(vLastLoadDateTime) )as TEST2 
      RESIDENT Serviceorders_temp
      WHERE if(Serviceorders.EventType = 1, num(timestamp([Serviceorders.Orderdatum]          +  [Serviceorders.Ordertijd])) > num($(vLastLoadDateTimeNUM)),
                  if(Serviceorders.EventType = 3, num(timestamp([Serviceorders.Einddatum]          +  [Serviceorders.Eindtijd])) > num($(vLastLoadDateTimeNUM)) 
                                                                                                  and
                                                                                                    num(timestamp([Serviceorders.Einddatum]          +  [Serviceorders.Eindtijd])) <= num(now())
                            )
                  )
      ;
      
      

       

       

      e.g.

       

      When I show after loading the vLastLoadDateTimeNUM as timestamp it shows:

      1-11-2012 11:15:12

       

      But when looking in the LOAD part the vLastLoadDateTimeNUM shows:

      1-11-2012 00:00:00

       

       

      Anyone has a clue what is happening here?

       

       

      thx in advance!!

       

      Anita

        • Re: Timestamp in load script, the time part is not what is should be.
          Gysbert Wassenaar

          You don't specify a format for num, so the format set in the operating system is used. In your case that seem to mean it discards the fraction. So you end up with a date without the time fraction.

            • Re: Timestamp in load script, the time part is not what is should be.
              Anita Fuchten

              But why is the variable itself containing the fraction part?? When showing this variable in the front side using timestamp it shows the correct value.

               

               

              Any idea how to solve this?? When I use the timestamp vLastLoadDateTime it gives errors.

               

              sample2.png

                • Re: Timestamp in load script, the time part is not what is should be.
                  Gysbert Wassenaar

                  I guess I'm going blind. I could have sworn that I read this:

                   

                  When I show after loading the vLastLoadDateTime as timestamp it shows:

                  1-11-2012 11:15:12

                   

                  But when looking in the LOAD part the vLastLoadDateTimeNUM shows:

                  1-11-2012 00:00:00

                   

                  If you wrote vLastLoadDateTimeNUM in both lines, then I'm obviously wrong.

                   

                  Anyway, the error... Aren't you missing an if somewhere in the where clause?

                  if(Serviceorders.EventType = 1,

                          num(timestamp([Serviceorders.Orderdatum] + [Serviceorders.Ordertijd])

                    ) <-- end of if statement

                  > num($(vLastLoadDateTimeNUM)), <-- what is compared to num(...) ?

                    • Re: Timestamp in load script, the time part is not what is should be.
                      Anita Fuchten

                      Maybe I really didn't explain it that well . . . it is about the vLastLoadDateTimeNUM which gives in the scripting side (loaded in the table as TEST) 1-11-2012 00:00:00 . . .

                      In the frontside i showed it in a textbox as a timestamp and it displays: 1-11-2012 11:15:12

                       

                       

                      The statement itself is correct . . . think you miscounted the '('  and ')' . . . you said

                      if(Serviceorders.EventType = 1,

                              num(timestamp([Serviceorders.Orderdatum] + [Serviceorders.Ordertijd])

                        ) <-- end of if statement

                      > num($(vLastLoadDateTimeNUM)), <-- what is compared to num(...) ?

                      but the <-- end of if statement you say is not the end of the if, but the end bracket of the num function ... ;-)

                       

                       

                      the story is that the num vLastLoadDateTimeNUM gives 2 different values.

                      on scripting side: 1-11-2012 00:00:00

                      on front side: 1-11-2012 11:15:12

                       

                      I really need the front side value at the scripting side . . . but can't see what is going wrong and why the time is set to 00:00:00 when using it in the load statement.

                       

                      thx

                        • Re: Timestamp in load script, the time part is not what is should be.
                          Stefan Wühl

                          In your where clause, you just need to compare numericals in the if() statement, so no need to format the numbers uisng num().

                           

                          Would be interesting to know how you defined vStartLOAD, but even without knowing, I believe your issue arise from how QV expands the dollar signe expansion.

                           

                          As you see, timestamp($(vLastLoadDateTimeNUM)) expands to timestamp(41214,50...).

                          You are probably using a decimal separator ','. But QVs internal decimal separator is '.' so this number will not be correctly interpreted, instead it will be interpreted at best as two arguments to function timestamp.

                           

                          Look into how QV handles dollar sign expansion and how you let QV return a legal decimal-point reflection using $(#vVariable).

                           

                          Depending on how vStartLOAD is defined, you can also try


                           

                           

                          WHERE if(Serviceorders.EventType = 1, ([Serviceorders.Orderdatum]          +  [Serviceorders.Ordertijd]) > $(vLastLOAD),
                                      if(Serviceorders.EventType = 3, ([Serviceorders.Einddatum]          +  [Serviceorders.Eindtijd]) > $(vLastLOAD)

                          and                                                           ([Serviceorders.Einddatum] +  [Serviceorders.Eindtijd]) <= now()
                                                )
                                      )
                          ;