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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Tab Delimiting Results - Conversion Error ?

Author  Topic 

mitin
Yak Posting Veteran

81 Posts

Posted - 2012-11-13 : 06:26:29
Hi,

When I run the following query:

[code]
select
firstoccurrence
+ char(9) +
lastoccurrence
+ char(9) +
servername
+ char(9) +
node
+ char(9) +
hostfunction
+ char(9) +
manager
+ char(9) +
tally
from data_table with (nolock)
where
firstoccurrence >= '2012-10-16 00.00.000'
and
lastoccurrence < '2012-10-17 00.00.000'
/code]

I get the following error:

"Conversion failed when converting datetime from character string.

However, if I replace the "+ char(9) +" after "firstoccurrence" and "lastoccurrence" with a comma, then I have no issues getting results, however I want a tab space between those two fields too, NOT a comma.

The datatype of the "firstoccurrence" and "lastoccurrence" columns in the DB is "(datetime, null).

What is the issue here? how can i modify the select statement to tab delimit the results, with a tab between EVERY column?

Many Thanks!!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-13 : 06:43:06
While concatenating columns that are not character type you have to convert those columns to varchar

select
cast(firstoccurrence as varchar(30))
+ char(9) +
cast(lastoccurrenceas varchar(30))
+ char(9) +
servername
+ char(9) +
node
+ char(9) +
hostfunction
+ char(9) +
manager
+ char(9) +
tally
from data_table with (nolock)
where
firstoccurrence >= '2012-10-16 00.00.000'
and
lastoccurrence < '2012-10-17 00.00.000'




--
Chandu
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 2012-11-13 : 08:10:05
Thanks very much!

However, the issue I now have is that the date is being displayed as, for example "May 22 2012 12:00AM" when I still want it to be displayed as '2012-05-22 00:00.000".

What can I do to have all the data tab delimited as it now is, but still have the date displayed the way that i want it to be in the results/.rpt files?

Also, I am now getting an awful lot of "NULL" returned instead of the rows of data, not good :( why is this?

Again, many thanks!
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-13 : 08:37:03
Instead of CAST, use CONVERT with the appropriate style (120 in your case). So, for example
CONVERT(VARCHAR(30),firstoccurrence ,120)
All the available styles are described here: http://msdn.microsoft.com/en-us/library/ms187928.aspx
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 2012-11-13 : 10:03:27
ok thanks, but when I do as you say above using CONVERT instead of CAST I now get this message:

error converting data type varchar to numeric

what needs to be done to get around this issue? thanks again
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-13 : 10:08:05
The message is not coming from replacing CAST with CONVERT. The message is an indication that there is at least one column that is of non-character type that you did not cast or convert to character type. Look through your columns in the list to see which. Or, go back to the query that was working and change one at a time to use convert instead of cast.
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 2012-11-13 : 10:26:12
I have looked through the columns in the table that is being queries here and can not find any fields that are of non char type other than the date fields, and I have followed your instructions to convert these.

Apart from firstoccurrence and lastoccurrence which are "datetime", all other fields are of the data type "varchar".

any ideas?
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-13 : 10:37:01
Try this and see if you get the same error?
SELECT CONVERT(VARCHAR(30), firstoccurrence, 120)
+ CHAR(9) +
CONVERT(VARCHAR(30), lastoccurrenceas, 120)
+ CHAR(9) +
CAST(servername AS VARCHAR(30))
+ CHAR(9) +
CAST(node AS VARCHAR(30))
+ CHAR(9) +
CAST(hostfunction AS VARCHAR(30))
+ CHAR(9) +
CAST(manager AS VARCHAR(30))
+ CHAR(9) +
CAST(tally AS VARCHAR(30))
FROM data_table WITH (NOLOCK)
WHERE firstoccurrence >= '20121016'
AND lastoccurrence < '20121017'
BTW, using "WITH (NOLOCK)" may get you wrong results from dirty reads.
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 2012-11-13 : 10:45:51
ok i will try, but i dont get why i have to cast the other columns to varchar when they're already of that data type.

But i will try thank you
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-13 : 10:52:23
If they are varchar you shouldn't have to - but then you should not get the error you are getting. Can you post the results of this query?
SELECT 
COLUMN_NAME,
DATA_TYPE
FROM INFORMATION_SCHEMA.[COLUMNS]
WHERE
TABLE_CATALOG = 'data_table'
AND TABLE_SCHEMA = 'dbo'
AND COLUMN_NAME IN
(
'firstoccurrence',
'lastoccurrenceas',
'servername',
'node',
'hostfunction',
'manager',
'tally'
);
Go to Top of Page
   

- Advertisement -