CONVERT function

[Autofreak]

Hi Guys,
I am importing a recordset into a OWC spreadsheet object that looks like

frmMainCash.srdShCusody.ActiveSheet.Cells.CopyFromRecordset rs_Data

rs_Data ie opened using a SELECT query and I would like to ask you a few questions about it


1. I need to see my numbers on the spreadsheet with decimal points and thousand commas like 1,000,000.00 . How do I achive that?

2. my datatime select statement looks like "SELECT CONVERT(datatime,[MD],101) FROM mY_table" where MD is a field with dates. Sometimes there's no date and the value is "0" or "" instead of say "20060303". In the zero/"" date case I get a message that the value can't be converted into datetime type. I would like to put the condition into the SELECT string that would convert MD if its value is other then "0"/"" but if MD is "0" assign/leave it "" so that the Excel cell is just blank.

Regards,
Serge

[Autofreak]

Hi Guys,
I am importing a recordset into a OWC spreadsheet object that looks like

frmMainCash.srdShCusody.ActiveSheet.Cells.CopyFromRecordset rs_Data

rs_Data ie opened using a SELECT query and I would like to ask you a few questions about it


1. I need to see my numbers on the spreadsheet with decimal points and thousand commas like 1,000,000.00 . How do I achive that?

2. my datatime select statement looks like "SELECT CONVERT(datatime,[MD],101) FROM mY_table" where MD is a field with dates. Sometimes there's no date and the value is "0" or "" instead of say "20060303". In the zero/"" date case I get a message that the value can't be converted into datetime type. I would like to put the condition into the SELECT string that would convert MD if its value is other then "0"/"" but if MD is "0" assign/leave it "" so that the Excel cell is just blank.

Regards,
Serge

Found the answers!
1. Can't be achieved in SELECT statement, the spreadsheet itself must be formatted for the thousand comma separators.
2. somth like this will work
select
case [MD]
when'0'then' '
elseconvert(varchar,convert(datetime, [MD], 101))
end
from my_Table

Bingo