Author |
Topic |
mitin
Yak Posting Veteran
81 Posts |
Posted - 2012-11-13 : 06:26:29
|
Hi,When I run the following query:[code]selectfirstoccurrence+ char(9) +lastoccurrence+ char(9) +servername+ char(9) +node+ char(9) +hostfunction+ char(9) +manager+ char(9) +tallyfrom data_table with (nolock)wherefirstoccurrence >= '2012-10-16 00.00.000'andlastoccurrence < '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 varcharselectcast(firstoccurrence as varchar(30))+ char(9) +cast(lastoccurrenceas varchar(30))+ char(9) +servername+ char(9) +node+ char(9) +hostfunction+ char(9) +manager+ char(9) +tallyfrom data_table with (nolock)wherefirstoccurrence >= '2012-10-16 00.00.000'andlastoccurrence < '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 exampleCONVERT(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 numericwhat 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_TYPEFROM INFORMATION_SCHEMA.[COLUMNS]WHERE TABLE_CATALOG = 'data_table' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME IN ( 'firstoccurrence', 'lastoccurrenceas', 'servername', 'node', 'hostfunction', 'manager', 'tally' ); |
|
|
|