SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Tab Delimiting Results - Conversion Error ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mitin
Yak Posting Veteran

81 Posts

Posted - 11/13/2012 :  06:26:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 11/13/2012 :  06:43:06  Show Profile  Reply with Quote
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 - 11/13/2012 :  08:10:05  Show Profile  Reply with Quote
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!

Edited by - mitin on 11/13/2012 08:19:42
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/13/2012 :  08:37:03  Show Profile  Reply with Quote
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 - 11/13/2012 :  10:03:27  Show Profile  Reply with Quote
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

Edited by - mitin on 11/13/2012 10:04:41
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/13/2012 :  10:08:05  Show Profile  Reply with Quote
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 - 11/13/2012 :  10:26:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/13/2012 :  10:37:01  Show Profile  Reply with Quote
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 - 11/13/2012 :  10:45:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/13/2012 :  10:52:23  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000