Error when using WHERE condition

[#1]

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.

[christian77 · Accepted solution]

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.

[#3]

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.

[bbi_mba_76]

You could use

...

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

;

[#5]

Thanks and it's working.

[#6]

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.

[jonathandienst]

Hi

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

[preminqlik]

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

try this