question

minerman avatar image
minerman asked

How to add quotes to each result returned in a column.

I am a newbie, so I apologize in advance. I have a query within a .odc file. I am trying to append quotations or something similar to a datetime variable, so that excel does not format the values as dates. I have tried: SELECT '"' + MYTABLE.MYDATETIME + '"' FROM.... But when I open this file, I get the error: "Data could not be retrieved from the database. Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again. If I do: SELECT MYTABLE.MYDATETIME FROM.... The query runs fine.
concatenation
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

minerman avatar image
minerman answered
Looks like I found an answer. I needed to put this in MS Access syntax. The function CStr() worked out for me. Sorry for the confusion.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Please put in your complete solution - and mark it as correct! That'll help anyone else with the same issue later.
1 Like 1 ·
KenJ avatar image
KenJ answered
You'll need to convert the datetime to a character type before you can append characters: SELECT '"' + CONVERT(VARCHAR(23), MYTABLE.MYDATETIME, 121) + '"' FROM
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Ken, I have tried this and I get the error: "Unidentified function 'CONVERT' in expression. I think I may have some limitations due to the environment that I'm working in. :( edit: I tried CAST too and no luck, but a different error.
0 Likes 0 ·
You can put your query with the converted datetime inside of a view and point the ODC connection to that view. I think you can also dispense with the quotes. After you convert it to a string, Excel should treat it as a string.
0 Likes 0 ·
@KenJ shouldn't that be `convert(varchar(10)...)` or some other number?
0 Likes 0 ·
I guess it should. It defaults to 30 so I don't usually worry about it unless I'm after something longer or more specific. I've updated the answer to varchar(23)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.