CONVERT a text column to date format

I have a column nvarchar(8) that I need to update to a date format, MM/DD/YYYY

Some of the values have 7 characters. the rest have 8 characters as shown below:

Col1

6051998

12061999

In both rows the format is M/DD/YYYY and MM/DD/YYYY respectively. I have tried using CONVERT and CAST but receive the error:

Conversion failed when converting datetime from character string.

I've manage to generate the correct format in a select statement using CASE:

SELECT date_updted =

CASE

WHEN(selectLEN(date_updted))= 7 THEN(SELECTLEFT((RIGHT(date_updted, 7)), 1)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4)))

ELSE(SELECTLEFT((RIGHT(date_updted, 8)), 2)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4)))

END

FROM Table1

How can perform an update of this column using the UPDATE statement? I've tried the following with no success:

UPDATE dbo.Table1

SET date_updted =(SELECT date_updted =

CASE

WHEN(selectLEN(date_updted))= 7 THEN(SELECTLEFT((RIGHT(date_updted, 7)), 1)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4))As date_updted)

ELSE(SELECTLEFT((RIGHT(date_updted, 8)), 2)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4))As date_updted)

END

FROM Table1)

FROM Table1

If your data is as strongly formated as you say, you should be able to convert to datetime with something like:

Code Snippet

select aDate,
convert(datetime, right(aDate, 4) + left( right('0'+aDate, 8), 4))
as convertedDT
from ( select '6051998' as aDate union all
SELECT '12061999'
) a

/*
aDate convertedDT
--
6051998 1998-06-05 00:00:00.000
12061999 1999-12-06 00:00:00.000
*/

|||

Try something like this:

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
DateCol varchar(8)
)

INSERT INTO @.MyTable VALUES ( '6051998' )
INSERT INTO @.MyTable VALUES ( '12061999' )

SELECT convert( datetime, ( stuff( stuff( right( '0' + DateCol, 8 ), 3, 0, '/' ), 6, 0, '/' )), 101 )
FROM @.MyTable

-
1998-06-05 00:00:00.000
1999-12-06 00:00:00.000

|||

Thanks for your responses and I'm sure these methods will work fine, however, my question really was how to use the UPDATE Statement to update the column in my initial post without creating a temp table or column. Maybe I missed something in your responses?

|||

Copy the expression from those select statement...

Code Snippet

UPDATE

dbo.Table1

SET

date_updted =convert(datetime, right(date_updted, 4) + left( right('0'+date_updted, 8), 4))

FROM

Table1

|||

Maybe:

Code Snippet

UPDATE dbo.Table1
SET date_updted
= convert(varchar(10),convert(datetime, right(date_updted, 4) + left( right('0'+date_updted, 8), 4)),101)

|||Very Nice! Thanks.

Control characters output in attachments from xp_sendmail

I have been testing our SQL Mail setup in SQL Server 2000 (sp3a) and
have found that when I attach results as a file, every other character
is a control character which causes each real character output on a
separate line. I have no idea why this is happenening, I've never seen
it before.
The code looks like this;

EXEC master.dbo.xp_sendmail
@.recipients = '<email address>',
@.dbuse = 'TestDB',
@.query = 'select top 50 descr from AdTable',
@.message = 'nathan email test',
@.subject='SQL Mail test',
@.attach_results = 'true',
@.width = 100,
@.separator = ','

The results look like this! -

d
e
s
c
r
-
-
-
-
-

etc.

When I view the result text file with an advanced text editor I can
see that every other character is a control - these characters are not
in the data, I have already checked this, so it looks like its they
are being created by SQL Server or the mail system? Any advice much
appreciated.

NathanHi

There is no DDL, but at a guess descr is a natural language datatype e.g
nchar or nvarchar. Try looking at convert/cast in books online to change it.

John
"Nathan Griffiths" <nathan@.griffiths.net> wrote in message
news:1ee31d7.0406011411.54e8da4f@.posting.google.co m...
> I have been testing our SQL Mail setup in SQL Server 2000 (sp3a) and
> have found that when I attach results as a file, every other character
> is a control character which causes each real character output on a
> separate line. I have no idea why this is happenening, I've never seen
> it before.
> The code looks like this;
> EXEC master.dbo.xp_sendmail
> @.recipients = '<email address>',
> @.dbuse = 'TestDB',
> @.query = 'select top 50 descr from AdTable',
> @.message = 'nathan email test',
> @.subject='SQL Mail test',
> @.attach_results = 'true',
> @.width = 100,
> @.separator = ','
> The results look like this! -
> d
> e
> s
> c
> r
> -
> -
> -
> -
> -
> etc.
> When I view the result text file with an advanced text editor I can
> see that every other character is a control - these characters are not
> in the data, I have already checked this, so it looks like its they
> are being created by SQL Server or the mail system? Any advice much
> appreciated.
> Nathan|||Hi John,

I don't think its to do with the data types as this strange formatting
occurs whatever the @.query parameter is set to e.g.

@.query = 'SELECT count(*) FROM Table1' is returned as;

-
-
-
-
-
-

5
7
8

The original data types were VARCHAR, there are no NVARCHAR columns in
the database. I'm stumped!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<bHXwc.2129$wx6.21830686@.news-text.cableinet.net>...
> Hi
> There is no DDL, but at a guess descr is a natural language datatype e.g
> nchar or nvarchar. Try looking at convert/cast in books online to change it.
> John
> "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> news:1ee31d7.0406011411.54e8da4f@.posting.google.co m...
> > I have been testing our SQL Mail setup in SQL Server 2000 (sp3a) and
> > have found that when I attach results as a file, every other character
> > is a control character which causes each real character output on a
> > separate line. I have no idea why this is happenening, I've never seen
> > it before.
> > The code looks like this;
> > EXEC master.dbo.xp_sendmail
> > @.recipients = '<email address>',
> > @.dbuse = 'TestDB',
> > @.query = 'select top 50 descr from AdTable',
> > @.message = 'nathan email test',
> > @.subject='SQL Mail test',
> > @.attach_results = 'true',
> > @.width = 100,
> > @.separator = ','
> > The results look like this! -
> > d
> > e
> > s
> > c
> > r
> > -
> > -
> > -
> > -
> > -
> > etc.
> > When I view the result text file with an advanced text editor I can
> > see that every other character is a control - these characters are not
> > in the data, I have already checked this, so it looks like its they
> > are being created by SQL Server or the mail system? Any advice much
> > appreciated.
> > Nathan|||Hi

If you save the attachment to disk as a text file does this still happen?

John

"Nathan Griffiths" <nathan@.griffiths.net> wrote in message
news:1ee31d7.0406072000.42435ac7@.posting.google.co m...
> Hi John,
> I don't think its to do with the data types as this strange formatting
> occurs whatever the @.query parameter is set to e.g.
> @.query = 'SELECT count(*) FROM Table1' is returned as;
> -
> -
> -
> -
> -
> -
> 5
> 7
> 8
> The original data types were VARCHAR, there are no NVARCHAR columns in
> the database. I'm stumped!
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<bHXwc.2129$wx6.21830686@.news-text.cableinet.net>...
> > Hi
> > There is no DDL, but at a guess descr is a natural language datatype e.g
> > nchar or nvarchar. Try looking at convert/cast in books online to change
it.
> > John
> > "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> > news:1ee31d7.0406011411.54e8da4f@.posting.google.co m...
> > > I have been testing our SQL Mail setup in SQL Server 2000 (sp3a) and
> > > have found that when I attach results as a file, every other character
> > > is a control character which causes each real character output on a
> > > separate line. I have no idea why this is happenening, I've never seen
> > > it before.
> > > The code looks like this;
> > > > EXEC master.dbo.xp_sendmail
> > > @.recipients = '<email address>',
> > > @.dbuse = 'TestDB',
> > > @.query = 'select top 50 descr from AdTable',
> > > @.message = 'nathan email test',
> > > @.subject='SQL Mail test',
> > > @.attach_results = 'true',
> > > @.width = 100,
> > > @.separator = ','
> > > > The results look like this! -
> > > > d
> > > e
> > > s
> > > c
> > > r
> > > -
> > > -
> > > -
> > > -
> > > -
> > > > etc.
> > > > When I view the result text file with an advanced text editor I can
> > > see that every other character is a control - these characters are not
> > > in the data, I have already checked this, so it looks like its they
> > > are being created by SQL Server or the mail system? Any advice much
> > > appreciated.
> > > > Nathan|||Yes, the attachment looks exactly the same when saved to disk, and
viewed with different Editors e.g. WordPad, ConText

If I set @.attach_results = 'false' to have the results returned in the
body of the e-mail, the format is fine - the problem appears to lie
with whatever writes the text file from the results, which I'm
guessing is the sqlmap70.dll?

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<mFfxc.874$Oj1.7196703@.news-text.cableinet.net>...
> Hi
> If you save the attachment to disk as a text file does this still happen?
> John
> "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> news:1ee31d7.0406072000.42435ac7@.posting.google.co m...
> > Hi John,
> > I don't think its to do with the data types as this strange formatting
> > occurs whatever the @.query parameter is set to e.g.
> > @.query = 'SELECT count(*) FROM Table1' is returned as;
> > -
> > -
> > -
> > -
> > -
> > -
> > 5
> > 7
> > 8
> > The original data types were VARCHAR, there are no NVARCHAR columns in
> > the database. I'm stumped!
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:<bHXwc.2129$wx6.21830686@.news-text.cableinet.net>...
> > > Hi
> > > > There is no DDL, but at a guess descr is a natural language datatype e.g
> > > nchar or nvarchar. Try looking at convert/cast in books online to change
> it.
> > > > John
> > > "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> > > news:1ee31d7.0406011411.54e8da4f@.posting.google.co m...
> > > > I have been testing our SQL Mail setup in SQL Server 2000 (sp3a) and
> > > > have found that when I attach results as a file, every other character
> > > > is a control character which causes each real character output on a
> > > > separate line. I have no idea why this is happenening, I've never seen
> > > > it before.
> > > > The code looks like this;
> > > > > > EXEC master.dbo.xp_sendmail
> > > > @.recipients = '<email address>',
> > > > @.dbuse = 'TestDB',
> > > > @.query = 'select top 50 descr from AdTable',
> > > > @.message = 'nathan email test',
> > > > @.subject='SQL Mail test',
> > > > @.attach_results = 'true',
> > > > @.width = 100,
> > > > @.separator = ','
> > > > > > The results look like this! -
> > > > > > d
> > > > e
> > > > s
> > > > c
> > > > r
> > > > -
> > > > -
> > > > -
> > > > -
> > > > -
> > > > > > etc.
> > > > > > When I view the result text file with an advanced text editor I can
> > > > see that every other character is a control - these characters are not
> > > > in the data, I have already checked this, so it looks like its they
> > > > are being created by SQL Server or the mail system? Any advice much
> > > > appreciated.
> > > > > > Nathan|||Hi

I am confused by this! Please post DDL (CREATE Table, CREATE Procedure
statements), example data as insert statements that will enable someone to
replicate the problem. Also SQL Server version details may help as well as
Windows regional setting.

Thanks

John
"Nathan Griffiths" <nathan@.griffiths.net> wrote in message
news:1ee31d7.0406131443.2191dbed@.posting.google.co m...
> Yes, the attachment looks exactly the same when saved to disk, and
> viewed with different Editors e.g. WordPad, ConText
> If I set @.attach_results = 'false' to have the results returned in the
> body of the e-mail, the format is fine - the problem appears to lie
> with whatever writes the text file from the results, which I'm
> guessing is the sqlmap70.dll?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<mFfxc.874$Oj1.7196703@.news-text.cableinet.net>...
> > Hi
> > If you save the attachment to disk as a text file does this still
happen?
> > John
> > "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> > news:1ee31d7.0406072000.42435ac7@.posting.google.co m...
> > > Hi John,
> > > > I don't think its to do with the data types as this strange formatting
> > > occurs whatever the @.query parameter is set to e.g.
> > > > @.query = 'SELECT count(*) FROM Table1' is returned as;
> > > > -
> > > -
> > > -
> > > -
> > > -
> > > -
> > > > 5
> > > 7
> > > 8
> > > > The original data types were VARCHAR, there are no NVARCHAR columns in
> > > the database. I'm stumped!
> > > > > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:<bHXwc.2129$wx6.21830686@.news-text.cableinet.net>...
> > > > Hi
> > > > > > There is no DDL, but at a guess descr is a natural language datatype
e.g
> > > > nchar or nvarchar. Try looking at convert/cast in books online to
change
> > it.
> > > > > > John
> > > > "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> > > > news:1ee31d7.0406011411.54e8da4f@.posting.google.co m...
> > > > > I have been testing our SQL Mail setup in SQL Server 2000 (sp3a)
and
> > > > > have found that when I attach results as a file, every other
character
> > > > > is a control character which causes each real character output on
a
> > > > > separate line. I have no idea why this is happenening, I've never
seen
> > > > > it before.
> > > > > The code looks like this;
> > > > > > > > EXEC master.dbo.xp_sendmail
> > > > > @.recipients = '<email address>',
> > > > > @.dbuse = 'TestDB',
> > > > > @.query = 'select top 50 descr from AdTable',
> > > > > @.message = 'nathan email test',
> > > > > @.subject='SQL Mail test',
> > > > > @.attach_results = 'true',
> > > > > @.width = 100,
> > > > > @.separator = ','
> > > > > > > > The results look like this! -
> > > > > > > > d
> > > > > e
> > > > > s
> > > > > c
> > > > > r
> > > > > -
> > > > > -
> > > > > -
> > > > > -
> > > > > -
> > > > > > > > etc.
> > > > > > > > When I view the result text file with an advanced text editor I
can
> > > > > see that every other character is a control - these characters are
not
> > > > > in the data, I have already checked this, so it looks like its
they
> > > > > are being created by SQL Server or the mail system? Any advice
much
> > > > > appreciated.
> > > > > > > > Nathan|||Hi John,

I have finally and quite by chance worked out what the problem is, and
wouldn't you know it, its caused by a bug in SQL Server 2000!

Basically, xp_sendmail returns Unicode data by default, a problem
fixed in SP1, but not updated in Books Online;

To return readable ANSI format results as a attachment I have to
include the additional xp_sendmail parameter:

@.ansi_attachment = 'true'

Its on the MS support website;

http://support.microsoft.com/defaul...kb;EN-US;280720

thanks anyway!

Nathan

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<fwdzc.4083$Rb4.33474736@.news-text.cableinet.net>...
> Hi
> I am confused by this! Please post DDL (CREATE Table, CREATE Procedure
> statements), example data as insert statements that will enable someone to
> replicate the problem. Also SQL Server version details may help as well as
> Windows regional setting.
> Thanks
> John
> "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> news:1ee31d7.0406131443.2191dbed@.posting.google.co m...
> > Yes, the attachment looks exactly the same when saved to disk, and
> > viewed with different Editors e.g. WordPad, ConText
> > If I set @.attach_results = 'false' to have the results returned in the
> > body of the e-mail, the format is fine - the problem appears to lie
> > with whatever writes the text file from the results, which I'm
> > guessing is the sqlmap70.dll?
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:<mFfxc.874$Oj1.7196703@.news-text.cableinet.net>...
> > > Hi
> > > > If you save the attachment to disk as a text file does this still
> happen?
> > > > John
> > > > "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> > > news:1ee31d7.0406072000.42435ac7@.posting.google.co m...
> > > > Hi John,
> > > > > > I don't think its to do with the data types as this strange formatting
> > > > occurs whatever the @.query parameter is set to e.g.
> > > > > > @.query = 'SELECT count(*) FROM Table1' is returned as;
> > > > > > -
> > > > -
> > > > -
> > > > -
> > > > -
> > > > -
> > > > > > 5
> > > > 7
> > > > 8
> > > > > > The original data types were VARCHAR, there are no NVARCHAR columns in
> > > > the database. I'm stumped!
> > > > > > > > > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:<bHXwc.2129$wx6.21830686@.news-text.cableinet.net>...
> > > > > Hi
> > > > > > > > There is no DDL, but at a guess descr is a natural language datatype
> e.g
> > > > > nchar or nvarchar. Try looking at convert/cast in books online to
> change
> it.
> > > > > > > > John
> > > > > "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> > > > > news:1ee31d7.0406011411.54e8da4f@.posting.google.co m...
> > > > > > I have been testing our SQL Mail setup in SQL Server 2000 (sp3a)
> and
> > > > > > have found that when I attach results as a file, every other
> character
> > > > > > is a control character which causes each real character output on
> a
> > > > > > separate line. I have no idea why this is happenening, I've never
> seen
> > > > > > it before.
> > > > > > The code looks like this;
> > > > > > > > > > EXEC master.dbo.xp_sendmail
> > > > > > @.recipients = '<email address>',
> > > > > > @.dbuse = 'TestDB',
> > > > > > @.query = 'select top 50 descr from AdTable',
> > > > > > @.message = 'nathan email test',
> > > > > > @.subject='SQL Mail test',
> > > > > > @.attach_results = 'true',
> > > > > > @.width = 100,
> > > > > > @.separator = ','
> > > > > > > > > > The results look like this! -
> > > > > > > > > > d
> > > > > > e
> > > > > > s
> > > > > > c
> > > > > > r
> > > > > > -
> > > > > > -
> > > > > > -
> > > > > > -
> > > > > > -
> > > > > > > > > > etc.
> > > > > > > > > > When I view the result text file with an advanced text editor I
> can
> > > > > > see that every other character is a control - these characters are
> not
> > > > > > in the data, I have already checked this, so it looks like its
> they
> > > > > > are being created by SQL Server or the mail system? Any advice
> much
> > > > > > appreciated.
> > > > > > > > > > Nathan

Contains Clause and Single Characters

I have a commerce server database that uses a full text index to search
product numbers and descriptions. My issue is that some of my product
numbers contain single characters, "EMT 1" for example, and the Contains
clause does not seem to treat this phrase as an exact match search when I do
something like
select *
from <table>
where contains(*, '"EMT 1"')
It finds that product and a number of other products that happen to contain
EMT in the description. Is there any way that I can get the index to
recognize the entire string? It seems that it treats the "1" as a noise word
and just drops it from the criteria.
I have tried stripping out all puncuation and white space from the product
number and adding that value to the FT Index but it complicates any Keword
searches that may have valid white space in that I can not universally strip
white space out of the criteria that the user entered to try to get a match.
Chris
cbuda wrote on Wed, 20 Jul 2005 06:54:06 -0700:

> I have a commerce server database that uses a full text index to search
> product numbers and descriptions. My issue is that some of my product
> numbers contain single characters, "EMT 1" for example, and the Contains
> clause does not seem to treat this phrase as an exact match search when I
> do something like
> select *
> from <table>
> where contains(*, '"EMT 1"')
> It finds that product and a number of other products that happen to
> contain EMT in the description. Is there any way that I can get the index
> to recognize the entire string? It seems that it treats the "1" as a
> noise word and just drops it from the criteria.
> I have tried stripping out all puncuation and white space from the product
> number and adding that value to the FT Index but it complicates any Keword
> searches that may have valid white space in that I can not universally
> strip white space out of the criteria that the user entered to try to get
> a match.
Edit the noise word file for the language you are using, removing everything
but leaving a single line with a space on it. Then rebuild the index -
everything will now be indexed.
Dan
|||Works perfectly, thanks!
"Daniel Crichton" wrote:

> cbuda wrote on Wed, 20 Jul 2005 06:54:06 -0700:
>
> Edit the noise word file for the language you are using, removing everything
> but leaving a single line with a space on it. Then rebuild the index -
> everything will now be indexed.
> Dan
>
>

Contains Characters or Numbers Method

Hello,

I was wondering if there is any method that I can use to determine if a field (defined as text) has any character fields or is really a number. I want to figure out if a field is truly all numeric, and out of curiosity, was wondering if there was a way in SQL or T-SQL.

Thanks.

look at SQL Server function IsNumeric. It return 1 if it is, otherwise 0. You can use it with CASE WHEN. If it is numeric, get it, otherwise, set to other value such as 0.

|||

You can check whether the following will work for you:

WHEREyourColumnNOTLIKE'%[^0-9]%'

or

WHERE IsNumeric(yourColumn)=1

|||

Hey,

I didn't know there was an IsNumeric method... and I'm using SQL 2000 so regular expression's won't work for me. Sorry I should have mentioned the 2000 part.

Thanks.

Constructing Email message

Hi,

I am constructing a Message (Body) for sending our Emails. It is around
3000 characters long. But for whatever reason, the last line seems to
be broken with a "!" exclamatory mark in it, which results in
displaying the constructed image path as a broken one.

How to resolve this ?. Thanks.

Regards,
KarthickNo idea - what version of SQL Server, how are you building the message
body, which data type are you using for the message body, how are you
going to send the mail, what does "image path" refer to, can you post a
simplified SQL script to show the problem etc.

As a complete guess, you've declared the message body as varchar but
the path has Unicode characters in it, so you need to use nvarchar. But
without more information, that's probably wrong.

Simon|||Simon, thanks for your reply.

I am using SQL Server 2000. The Message body is NTEXT datatype in the
database. And I am constructing the message body like the following:

SELECT @.MessageBody = '<!DOCTYPE HTML PUBLIC "-//IETF//DTD
HTML//EN">'
SELECT @.MessageBody = @.MessageBody + '<html><head><meta
http-equiv="Content-Type" '
SELECT @.MessageBody = @.MessageBody + 'content="text/html;charset=' +
@.CharSet + '">'
SELECT @.MessageBody = @.MessageBody + '<title>Title goes here:
Ticket</title>'
SELECT @.MessageBody = @.MessageBody + '<link rel="stylesheet"
type="text/css" '

-- I have 67 lines of the @.MessageBody construction and at the end of
the stored procedure I am doing a EXEC to insert this @.MessageBody onto
another table and our third party vendor picks up the Emails from the
table and send them out. So I don't send out the emails manually, all I
do is insert the email contents onto a table and the rest is taken care
of.

Please help. Thanks.

Karthick|||It's still a little unclear (at least to me) - @.MessageBody can't be
ntext, because you can't declare an ntext variable. And you don't say
where the invalid data appears - if you SELECT @.MessageBody before
INSERTing it, is the data correct? Or is it only wrong after INSERTing?
And what does "doing an EXEC" mean? Are you using dynamic SQL, or a
stored procedure to do the INSERT?

Rather than describing your problem, I suggest that you try to produce
a (simplified) script to illustrate your problem - code is always
clearer than a description, and if other people can quickly copy and
paste into Query Analyzer, you're more likely to get a useful answer.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon