topic Re: Error when using WHERE condition in QlikView

Error when using WHERE condition

Mon, 07 Oct 2013 08:21:50 GMT

Hi,

I need to load data from SQL table. So, I used

Load

emp,

company

SQL SELECT *

FROM "XX".Dbo."Employee"

where CreatedOn>'2005-12-30';

when I am using WHERE condition I am getting following error.

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Could anyone help me how to solve this and how to get the values after 2005.

Thanks.

Re: Error when using WHERE condition

christian77 — Mon, 07 Oct 2013 08:25:08 GMT

Hi:

It's clearly a question of date format. What DB are you extracting data from.

Use a cast(Date) in you DB to change that format.

Luck.

Re: Error when using WHERE condition

Mon, 07 Oct 2013 08:27:41 GMT

Thanks. Could you please let me know how to change the data format in DB as I am using SQL Database. The datatype for CreatedOn is datetime. And also I don't have write permissions to change the format in SQL database.

Re: Error when using WHERE condition

bbi_mba_76 — Mon, 07 Oct 2013 08:52:56 GMT

You could use

...

where CreatedOn>convert(datetime, '2005-12-30', 120)

;

Re: Error when using WHERE condition

Mon, 07 Oct 2013 08:59:12 GMT

Thanks and it's working.

Re: Error when using WHERE condition

Mon, 07 Oct 2013 09:00:32 GMT

Thanks it's working. In the same table I have huge amount of records. So, it's taking too long to load into QVD. Could you please help me how to increase the performance in this case.

Re: Error when using WHERE condition

jonathandienst — Mon, 07 Oct 2013 09:00:35 GMT

With SQL server, if the date is in a format that the DB recognises, you can use a quoted value like yours. The error is because SQL Server does not recognise that format.

You can use Convert(') to convert a specific format. From SQL Books Online

Syntax for CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

So, data_type would be datetime. Expression is your quoted string. The style is the format of the expression. You can look up the styles in SQL Books Online.

Hope that helps

Jonathan

Re: Error when using WHERE condition

preminqlik — Mon, 07 Oct 2013 12:58:39 GMT

where num(date#(CreatedOn))>num(makedate(2005,12,30))

try this