WHERE clause for a varchar field

dataservices job design
1

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 !!

please advice…

thanks…

BOB_US (BOB member since 2006-03-16)

2

to_char
[i]Converts a date or numeric data to a string. It supports the Oracle 9i timestamp data type up to 9 digits precision for sub-seconds.

Syntax
to_char(date or numeric_expression, format)

Return value
varchar[/i]

data_guy :us: (BOB member since 2006-08-19)

3

Thanks for the reply Data_guy…

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.>.

BOB_US (BOB member since 2006-03-16)

4

A where clause in a query is not a SQL Server syntax, it is the DataServices syntax. And maybe gets pushed down into SQL Server if possible.

to_date(RECEIVEDDATE, ‘yyyy.mm.dd’)=to_date(‘2009.08.10’, ‘yyyy.mm.dd’)

But if RECEIVEDDATE is always in that format, why not compare the strings?

RECEIVEDDATE = ‘08/10/2009’

Werner Daehn :de: (BOB member since 2004-12-17)

5

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…

:slight_smile:

BOB_US (BOB member since 2006-03-16)

6

:+1:

Got it.

Werner Daehn :de: (BOB member since 2004-12-17)