Dynamic Update and Dates

[#1]

Hi There,

Hope someone can help. Each night a new set of QVD files are generated. Some applicaltions we use require live data so we have been looking at using the Dynamic update function.

When retrieving the field DateUpdated from the SQL Server and adding it to the DateUpdated Table within Qlikview using DynamicUpdate the dates seem to appear in a different format. Using a list box it would appear that one is 12hr and one is 24hr and therefore any calculations we do on DateUpdated appears to exclude data for the current date.

The Macro i use to retive the DateUpdate is as follows

Set oRS =CreateObject("ADODB.Recordset")
sSQL="Select ID,DateUpdated,UserID,CAST(Closed AS INT) * - 1 AS Closed,CompanyID,EmployeeID,Priority from [WORK] WHERE DateUpdated>dateadd(minute,-20,'" & WorkDate & "') OR DateCreated>dateadd(minute,-20,'" & WorkDate & "')"
oRS.Open sSQL, oconn,3,2
 Values = oRS.GetRows
 ' Opens table
 ' Pulls all the rows back into an array called Values
 ' Closes connection

 oRS.close
 set sConn=nothing
 For r = LBound(Values, 2) To UBound(Values, 2)

 SET Inputting = ActiveDocument.DynamicUpdateCommand ("DELETE FROM QVWork WHERE WorkID=" & Values(0, r))
 QlikSQL = "INSERT INTO QVWork (WorkID,DateUpdated,UserID,Closed,CompanyID,EmployeeID,Priority) VALUES (" & Values(0, r) & ",'" & Values(1, r) & "'," & Values(2, r) & "," & Values(3, r) & "," & Values(4, r) & "," & Values(5, r) & "," & Values(6, r) & ")"
 SET Inputting = ActiveDocument.DynamicUpdateCommand (QlikSQL)

How can I make sure that the date retrieved in the Macro is formatted the same as the date in the QVD file, I'm assuming that it's the difference between Qlikview SQL connector and the Macro Interpreter?

Any Pointers will be greatly Received!

[#2]

I would convert different formatted dates into number. In VBScript, you can use CDbl. Or something like this CDbl(CDate(<date string >))

[#3]

Unfortuantly Qlikview doesnt seem to support this command within the Macro, Unless im missing something?

Thanks for the reply.

Gez

[#4]

QlikView Macro can be written on pure JavaScript or pure VBScript. You can use any VBScript functions in QlikView macro.