Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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.
You could use
...
where CreatedOn>convert(datetime, '2005-12-30', 120)
;
Thanks and it's working.
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.
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
where num(date#(CreatedOn))>num(makedate(2005,12,30))
try this