topic Date Format problem with SQL Server in QlikView

Date Format problem with SQL Server

Fri, 22 Oct 2010 18:38:23 GMT

Hi to all,
It's all day that I'm fighting with this easy script but it doesn't want to work...

LET LastExecTime = ReloadTime();
LET BeginningThisExecTime = Now( );

Tab_test:

Select Id,
Nome,
Data_mod
FROM Galli.dbo.test;

WHERE Data_mod > $(LastExecTime)
AND Data_mod < $(BeginningThisExecTime)
Concatenate (Tab_test) LOAD * FROM Tabella_test.qvd(qvd);
STORE Tab_test INTO Tabella_test.qvd(qvd);

Data_mod in SQL Server it's a datetime field with GETDATE () as default value

the problem is the where condition but I don't know why...

any ideas ?

Thx

Andrea

Date Format problem with SQL Server

Miguel_Angel_Baeyens — Fri, 22 Oct 2010 19:16:18 GMT

Hello Andrea,

They are likely in different time formats. Use date() format instead:

LET LastExecTime = Date(ReloadTime());LET BeginningThisExecTime = Date(Now()); Tab_test:LOAD Id, Nome, Data_modWHERE Date(Data_mod) > $(LastExecTime) AND Date(Data_mod) < $(BeginningThisExecTime)Select Id, Nome, Data_modFROM Galli.dbo.test;Concatenate (Tab_test) LOAD * FROM Tabella_test.qvd(qvd);STORE Tab_test INTO Tabella_test.qvd(qvd);

You will have to do it in the LOAD part, unless you know you are formating both in the same way (i. e.: DD-MM-YYYY)

Hope that helps!

Date Format problem with SQL Server

jonathandienst — Fri, 22 Oct 2010 19:39:56 GMT

Andrea

Miguel is right in that it is probably the date formatting, but I think you can do this in the SQL SELECT part as well, which will have the advantage of reducing the number of records being sent by the server. You also have syntax errors (; before the WHERE clause and no ; before the CONCATENATE). The dates are being passed as strings, so they need to be quoted.

Most servers accept dates/times in yyyy/MM/dd hh:mm:ss or yyyy-MM-dd hh:mm:ss format, so I would try those first. I suggest that you try:


LET LastExecTime = Date(ReloadTime(), 'yyyy/MM/dd hh:mm:ss');
LET BeginningThisExecTime = Date(Now( ), 'yyyy/MM/dd hh:mm:ss');
Tab_test:
Select Id,
 Nome,
 Data_mod
FROM Galli.dbo.test
WHERE Data_mod > '$(LastExecTime)'
 AND Data_mod < '$(BeginningThisExecTime)'
;
Concatenate (Tab_test)
LOAD * FROM Tabella_test.qvd(qvd);

STORE Tab_test INTO Tabella_test.qvd(qvd);

You coud also try yyyy-MM-dd hh:mm:ss for the format string if the code above does not work.

Hope that helps

Jonathan

Date Format problem with SQL Server

Tue, 26 Oct 2010 12:46:52 GMT

Great it works!!!

Thx

Andrea