SQL Syntax Check

[samuel_brierley]

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

[jonathandienst]

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;

[jonathandienst · Accepted solution]

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.

[samuel_brierley]

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?

[jonathandienst]

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.

[samuel_brierley]

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'

[samuel_brierley]

any ideas of whats gone wrong?

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