SQL Select via DTS to Excel file

Posted on 2003-03-20
Medium Priority
Last Modified: 2012-06-22
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
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

Question by:jvanmarter
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 8179771
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....

Author Comment

ID: 8181254
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.

Accepted Solution

xenon_je earned 100 total points
ID: 8193592
Try with a workaround:
use a second field which signals if you had a null value. Something like this:

  IsNull(yourDateField,'1977-01-01 00:00:00'),
  (case when yourDateField is null then 0 else 1 end)
from yourTable

and when you make checkings, you use the second field too...
good luck....xenon

Expert Comment

ID: 9275660
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 
Post your closing recommendations!  No comment means you don't care.

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question