asked on

SQL Select via DTS to Excel file

I'm having trouble maintaining the format setting of a field in the Excel I'm exporting to using a DTS package. I am exporting out a datetime field and that field in the Excel file is formatted as Time only. The Select and Excel results sample is listed below. The first three rows show up in the Excel file as a TIME format 6:00 PM and are in fact Time format, but when the process gets to a null, all rows after that lose the TIME format and are treated as TEXT thereafter. Does anyone have a solution on how I can keep time format after NULLS exist?
SQL Excel gets
01/01/2003 18:00:00 6:00PM
01/01/2003 18:00:00 6:00PM
01/01/2003 18:00:00 6:00PM
NULL
NULL
NULL
01/01/2003 18:00:00 01/01/2003
01/01/2003 18:00:00 01/01/2003
01/01/2003 18:00:00 01/01/2003

Jv

xenon_je

Do something like this:
when you export, do not export from the table, but from a query.

The query should be:

select..., IsNull(YourDateFieldName, '1977/01/01 00:00:00') as YourDateFieldName, ...

This ay everywhere the null appears will appear the time 00:00:00.....
Hope this helps....
gl...xenon

jvanmarter

ASKER

Thanks for the option.. I'm currently using a SELECT and can populate 00:00 in the field when they are blank to prevent the issue, however, many records use the value 00:00 as the actual value and I don't want to show values that don't exist for that row. The idea behind the DTS package is that a user fires off a Report request over the WEB page I have, that request goes into the Report_Print_Queue table, where a job runs every minut to check to see if any request have come in. If so, it fires up the DTS which selects out the data base on the parameters that the used pass with request, runs the query, then puts the results into an Excel file and then XP-SendMails the file it just created. Fully autmated for the user, so I won't be able to massage results for the user.

ASKER CERTIFIED SOLUTION

xenon_je

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CleanupPing

jvanmarter:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.