Tab Delimiting Results - Conversion Error ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

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!

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

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

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.

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?

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.

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

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'
	);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources