topic Re: SQL Syntax Check in QlikView

SQL Syntax Check

samuel_brierley — Mon, 21 Sep 2015 11:09:54 GMT

Hi all,

can someone please have a quick check of my SQL Syntax?

[Fault List]:

SQL SELECT *

FROM AFMS.dbo."All_Faults" WHERE "Raised_Time >" $(Last_Updated_Date) "OR Reported_Time >" $(Last_Updated_Date);

store [Fault List] into Fault List.qvd;

thanks

Re: SQL Syntax Check

jonathandienst — Mon, 21 Sep 2015 11:17:48 GMT

I think

[Fault List]:

SQL SELECT *

FROM AFMS.dbo.All_Faults WHERE Raised_Time > '$(Last_Updated_Date)' OR Reported_Time > '$(Last_Updated_Date)';

Store [Fault List] into [Fault List].qvd;

Re: SQL Syntax Check

jonathandienst — Mon, 21 Sep 2015 11:19:58 GMT

I am assuming that the variable Last_Updated_Date contains the date in a text format recognised by the DB server as a date and can be implicitly converted to a datetime (or equivalent) SQL value.

Re: SQL Syntax Check

samuel_brierley — Mon, 21 Sep 2015 11:39:09 GMT

Hi Jonathan,

the last_Updated_Date is a timestamp as text in the database that Qlikview then converts to a date/TIme.and stores as a variable, i was assuming that if i then put my Where Clause using the variable it will still work, is this not the case?

Re: SQL Syntax Check

jonathandienst — Mon, 21 Sep 2015 11:41:42 GMT

Yes, if the date in a text format recognised by the DB server as a date and can be implicitly converted to a datetime (or equivalent) SQL value.

Re: SQL Syntax Check

samuel_brierley — Mon, 21 Sep 2015 11:41:52 GMT

ahh you werent wrong

SQL##f - SqlState: 22008, ErrorCode: 242, ErrorMsg: [Microsoft][SQL Server Native Client 11.0][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

[Fault List]:

SQL SELECT *

FROM AFMS.dbo.All_Faults WHERE Raised_Time > '21/09/2015 12:07:44' OR Reported_Time > '21/09/2015 12:07:44'

Re: SQL Syntax Check

samuel_brierley — Mon, 21 Sep 2015 11:45:06 GMT

any ideas of whats gone wrong?

im thinking lunch then try and get to the bottom of this.