convert a datatype

i have table tt.
it contains two fields one is ttno int,doj datetime . i want to
convert to datetime to varchar .
how it is ... give me some examplesNo problem, look at the CONVERT function for a specific format you want
to extract. I prefer using the ISO one, though its good for ordering
and international.

CONVERT(VARCHAR(10),GETDATE(),112)

HTH, jens Suessmeyer.

--
http://www.sqlserver2005.de
--|||Check the topic CONVERT in SQL Server Books Online.

--
Anith|||Anith Sen (anith@.bizdatasolutions.com) writes:
> Check the topic CONVERT in SQL Server Books Online.

Actually, the topic is CAST and CONVERT, lest someone looks in the wrong
place and don't find it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

conversion to date

HI everyne,

I have a varchar field in one table, which contains data in the form '010706' and I want to convert this to date datatype to 01/07/2006 (Jan 07, 2006). When I just import the data to the other table it gets converted to 7/6/2001, how can I convert it right? Please help.

CAST(RIGHT(datafield,2)+LEFT(datafield,4) AS datetime)

|||Thanks Motley. It works!

Conversion problems between mssql and access

Hello

When I trying to insert data with datatype datetime or smalldatetime from SQL Server into a table in a linked access database I get this error :

Server: Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type smalldatetime to float is not allowed. Use the CONVERT function to run this query.

I dont understand why it try to insert it as a float?!Because from a SQL Server point of view DateTime values ARE float values.

So you cannot do an implict cast but you have to do explictly by using the T-SQL statement "CONVERT"

Look in Books On Line for better help on conversions.|||Hello

I have tried CONVERT. But I dont know to which datatype I should convert my source value.
I have converted to varchar and nvarchar but I still got the same error.|||can you post your code, and specify from what kind of data type you what convert to?|||INSERT INTO LINKEDACCESS...ContactTarget
Select ChangedBy, ChangedDate, PersonIdNo,IdNo,TargetCode,TargetCodeproductCode,T argetCodePotentialCode
from tblContactTarget
WHERE Id NOT IN(select Id from LINKEDACCESS...ContactTarget CT
WHERE CT.Target = tblContactTarget.TargetCode)

ChangedDate is of datatype Datetime in SQL and Date/Time in Access.
This INSERT will trigger the error I wrote about.|||Try to convert it to a varchar. use the CONVERT function so that you can even specify the dateformat you need to have

CONTAINSTABLE inconsistency with phrase

Hi

I have a table in Sql Server 2000 with full text indexing setup on a column called 'contents' with a datatype of Text.

The column contains HTML, and I want to search for a particular link eg.

<a href="/hm/default.aspx?i=40559#secure">

..by using the following phrase:

/hm/default.aspx?i=40559

..in a CONTAINSTABLE query.

I need to find the phrase within the html. This is tricky because containstable only allows a wildcard at the end of the phrase, ie I can't search for '*/hm/default.aspx?i=40559*' with an asterisk at each end.

BUT, when I try the following query on my development server, it works without any asterisks:

SELECT item_id, contents, a.RANK FROM
CONTAINSTABLE(item, contents, '/hm/default.aspx?i=40559') as a, item b
WHERE (a.[KEY] = b.item_id AND path LIKE '%10646%')

The row is returned but I don't understand why. I thought the full text would try to find the phrase on its own (with no html surrounding it)? On my live server, it doesn't work (also Sql 2000). The data and the FT catalogs are the same on both machines. What else could cause this difference?

Any help greatly appreciated.

Ed

You get the data is because of the join. Try running just the containstable() query by itself and you should notice it.

|||

Thanks.

I tried it without the join as

SELECT item_id, contents FROM
CONTAINSTABLE(item, contents, '/hm/default.aspx?i=40559') as a, item b

..but it's not returning the row on my live server. It works ok on my dev server which seems odd?

|||

What do you get for these?

SELECT * FROM CONTAINSTABLE(item, contents, '/hm/default.aspx?i=40559') as a

select * from item b where , item b
WHERE LIKE '%10646%'

Also, your original query can be rewriten as this.

SELECT * FROM CONTAINSTABLE(item, contents, '/hm/default.aspx?i=40559') as a

join item b on (a.[KEY] = b.item_id AND b.path LIKE '%10646%')

Do you see why you get more rows returned now.

|||

Thanks for your help oj.

I tried

SELECT * FROM CONTAINSTABLE(item, contents, '/hm/default.aspx?i=40559') as a

..but no rows returned.

Works on my dev server no problem, row IS returned.

Still unsure about what's causing this. I tried your rewrite of the query but it's not returning the row either. Am I right in thinking that containstable searches for words (ie with a space at either end) or word prefixes, and if so, why is this working on my dev server in the first place?

Thanks again.

|||

Perhaps, there is no such row exist. Try

SELECT * FROM item

where contents like '/hm/default.aspx?i=40559%'

Also, try update/repopulate your fts.

|||

Thanks oj

The LIKE query returns the row just fine:

SELECT * FROM item
where contents like '%/hm/default.aspx?i=40559%'

I've tried rebuilding and repopulating the catalogs (several times) but it still won't work. One thing I should mention is I deleted the contents of my noise.dat and noise.eng files, but I don't think that's significant.

It seems the only option is to grab the column and do a string.indexof('phrase') method in my code, which is annoying. Still don't get why it works on one envrionment and not the other.

Ed

CONTAINSTABLE and wildcard inconsistency

I have a table in Sql Server 2000 with full text indexing setup on a column
called 'contents' with a datatype of Text.
The column contains HTML, and I want to search for a particular link eg.
<a href="http://links.10026.com/?link=/hm/default.aspx?i=40559#secure">
...by using the following phrase:
/hm/default.aspx?i=40559
...in a CONTAINSTABLE query.
I need to find the phrase within the html. This is tricky because
containstable only allows a wildcard at the end of the phrase, ie I can't
search for '*/hm/default.aspx?i=40559*' with an asterisk at each end.
BUT, when I try the following query on my development server, it works
without any asterisks:
SELECT item_id, contents, a.RANK FROM
CONTAINSTABLE(item, contents, '/hm/default.aspx?i=40559') as a, item b
WHERE (a.[KEY] = b.item_id AND path LIKE '%10646%')
The row is returned but I don't understand why. I thought the full text
would try to find the phrase on its own (with no html surrounding it)? On my
live server, it doesn't work (also Sql 2000). The data and the FT catalogs
and the word-breaker are the same on both machines. What else could cause
this difference?
Any help greatly appreciated.
Ed
This could be a version issue, what are the results of select @.@.version from
both servers.
On my machine, using us_english, I get <a
href="/hm/default.aspx?i=40559#secure">
indexed and queried as a, href, hm, default, aspx, i, 40559, and secure.
I would also check the noise word lists as Daniel suggests to make sure they
are identical.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"edwaldo" <edwaldo@.discussions.microsoft.com> wrote in message
news:C93D95AE-3296-42CD-B42E-10FCC841369F@.microsoft.com...
>I have a table in Sql Server 2000 with full text indexing setup on a column
> called 'contents' with a datatype of Text.
> The column contains HTML, and I want to search for a particular link eg.
> <a href="http://links.10026.com/?link=/hm/default.aspx?i=40559#secure">
> ..by using the following phrase:
> /hm/default.aspx?i=40559
> ..in a CONTAINSTABLE query.
> I need to find the phrase within the html. This is tricky because
> containstable only allows a wildcard at the end of the phrase, ie I can't
> search for '*/hm/default.aspx?i=40559*' with an asterisk at each end.
> BUT, when I try the following query on my development server, it works
> without any asterisks:
> SELECT item_id, contents, a.RANK FROM
> CONTAINSTABLE(item, contents, '/hm/default.aspx?i=40559') as a, item b
> WHERE (a.[KEY] = b.item_id AND path LIKE '%10646%')
> The row is returned but I don't understand why. I thought the full text
> would try to find the phrase on its own (with no html surrounding it)? On
> my
> live server, it doesn't work (also Sql 2000). The data and the FT
> catalogs
> and the word-breaker are the same on both machines. What else could cause
> this difference?
> Any help greatly appreciated.
> Ed
>
|||Hilary/Daniel,
Thank you both for your help with this.
To reiterate, on server A the containtable does return the row. Version is
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Developer Edition on
Windows NT 5.1 (Build 2600: Service Pack 1)
On server B it doesn't. Version is
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Standard Edition on
Windows NT 5.0 (Build 2195: Service Pack 4)
I tried splitting the phrase as you suggested:
CONTAINSTABLE(item, contents, '"hm default aspx i 40559"')
...works on server A, not on server B. Which is a shame because this would
be easy to implement.
On both machines the data is the same. On both machines I cleared the
contents of the noise files - noise.dat and noise.eng are both empty. I then
created the FT catalogs using the English (United Kingdom) word breaker. I
have repopulated each several times, and a simple LIKE query returns the row
on both servers.
I appreciate why it shouldn't work on server B, but I'm intrigued as to why
it works on server A at all!
Thanks again.
Ed
"Hilary Cotter" wrote:

> This could be a version issue, what are the results of select @.@.version from
> both servers.
> On my machine, using us_english, I get <a
> href="http://links.10026.com/?link=/hm/default.aspx?i=40559#secure">
> indexed and queried as a, href, hm, default, aspx, i, 40559, and secure.
> I would also check the noise word lists as Daniel suggests to make sure they
> are identical.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "edwaldo" <edwaldo@.discussions.microsoft.com> wrote in message
> news:C93D95AE-3296-42CD-B42E-10FCC841369F@.microsoft.com...
>
>
|||The problem is that you are using different word breakers. Apply Sp4 on both
SQL Server versions to get consistent behavior.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"edwaldo" <edwaldo@.discussions.microsoft.com> wrote in message
news:6F1AE47C-DD30-4AF5-AC45-9F61C73710E5@.microsoft.com...[vbcol=seagreen]
> Hilary/Daniel,
> Thank you both for your help with this.
> To reiterate, on server A the containtable does return the row. Version
> is
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Developer Edition on
> Windows NT 5.1 (Build 2600: Service Pack 1)
> On server B it doesn't. Version is
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Standard Edition on
> Windows NT 5.0 (Build 2195: Service Pack 4)
> I tried splitting the phrase as you suggested:
> CONTAINSTABLE(item, contents, '"hm default aspx i 40559"')
> ..works on server A, not on server B. Which is a shame because this would
> be easy to implement.
> On both machines the data is the same. On both machines I cleared the
> contents of the noise files - noise.dat and noise.eng are both empty. I
> then
> created the FT catalogs using the English (United Kingdom) word breaker.
> I
> have repopulated each several times, and a simple LIKE query returns the
> row
> on both servers.
> I appreciate why it shouldn't work on server B, but I'm intrigued as to
> why
> it works on server A at all!
> Thanks again.
> Ed
>
> "Hilary Cotter" wrote: