| Author |
 Topic  |
|
|
mitin
Yak Posting Veteran
81 Posts |
 Posted - 11/13/2012 : 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
Flowing Fount of Yak Knowledge
India
1687 Posts |
Posted - 11/13/2012 : 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 - 11/13/2012 : 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! |
Edited by - mitin on 11/13/2012 08:19:42 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
|
|
mitin
Yak Posting Veteran
81 Posts |
Posted - 11/13/2012 : 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 |
Edited by - mitin on 11/13/2012 10:04:41 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/13/2012 : 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 - 11/13/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/13/2012 : 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 - 11/13/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/13/2012 : 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'
); |
|
|
| |
Topic |
|
Tab Delimiting Results - Conversion Error ?
