Trying to operate with NTEXT Column Data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trying to operate with NTEXT Column Data

Author

Topic

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-01-12 : 06:03:59

I have a SQL Query as such
SELECT form_data_id, form_id, content_language, form_field_name, form_field_value, user_id, date_created, date_modified
FROM form_data_tbl
WHERE (user_id = 30) AND (form_field_name = 'BookingDate') AND (form_field_value > '01012009')

And I get the Error
The data types Ntext and varchar are incompatible in the greater operation.

Now I am unable to change the type of column so I was wondering if there is anyway around this ?

I am trying to check that the value in the column will be greater than a certain date..

Also I would like to order by form_field_value by date desc but again it is a NTEXT column so unable to..

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-01-12 : 07:30:56

You can cast the ntext column to varchar and then compare as in

cast(form_field_name as varchar(max)) = 'BookingDate'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 07:33:15

Also from 2005 onwards text,ntext are deprecated so remember to use varchar(max),nvarchar(max) instead.

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 08:23:03

I wonder if

WHERE form_field LIKE 'BookingDate%'

or

WHERE PATINDEX('BookingDate%', form_field) <> 0

would be faster than a CAST / CONVERT from TEXT to VARCHAR ??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources