Hi, I am using DI to restrict a particular set of Data from my input.
The data is date and time, But unfortunately, when the table was designed the column type was 'varchar(10)" and now i need to get a set of date only from 08/01/2009 to 08/15/2009. I have tried using the “where tab” in the Query properties, but this being a varchar, it doesn’t retrieve correct results.
The DB is SQL server 2005.
Is it possible to use SQL server command in DI like… CONVERT(VARCHAR(10), receiveddate, 101)= ‘08/07/2009’
Another solution I tried which didn;t work was using a filter in the connection between the table and the “query transform” which retrieved all records !!
I dont think i understood your reply clearly. I dont want to convert the data into a string. It is already in a string format and i want to apply date functions to it.
for ex:
select * from [order] where cast(receiveddate as datetime) =‘08/10/2009’
the above syntax works fine in SQL server but when the same “where” clause is used in the “query properties” in DI, i get the below error.
[Query:Query]
Invalid WHERE clause. Additional information: <Syntax error at line <1>: <>: near found expecting <’)’, ‘,’>.
1 error(s), 0 warning(s).
. Please check and fix the syntax and retry the operation. (BODI-1112394)>. (BODI-1111078)
Hope this helps…
Update…
I even tried this function in DI
to_date(RECEIVEDDATE, ‘yyyy.mm.dd’)=‘2009.08.10’
But it throws the error…
[Query:Query]
Invalid WHERE clause. Additional information: <The constant string <‘2009.08.10’> cannot be converted to a valid . The format is yyyy.mm.dd for dates, hh24:mi:ss for time, and yyyy.mm.dd hh24:mi:ss for datetime.>.
Thanks wdaehn,
that syntax worked… The reason i could not compare the strings was because i would have to add a condition on a date search range, so i had to convert it to a date initially…