Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

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
0
jvanmarter
Asked:
jvanmarter
  • 2
1 Solution
 
xenon_jeCommented:
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
0
 
jvanmarterAuthor Commented:
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.
0
 
xenon_jeCommented:
Try with a workaround:
use a second field which signals if you had a null value. Something like this:

Select
  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
0
 
CleanupPingCommented:
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now