none
Unpredictable and wrong date format in csv sent from sp_send_dbmail RRS feed

  • Question

  • these are datetime2() format from SQL:

    2019-11-01 10:44:38.0000000
    2019-11-01 10:44:40.0000000
    2019-11-01 10:44:39.0000000
    2019-11-01 10:44:38.0000000

    But when SELECT and send out to csv using sp_send_dbmail and CONVERT(VARCHAR(40), [ORDER_INIT_TS], 120),

    some of the dates appear as wanted but some are this

    39:10.0
    42:55.0
    42:56.0

    which can be format in Excel to proper format. 

    but why the inconsistency?

    thank you in in advance for your help!!

    Tuesday, November 12, 2019 8:02 PM

All replies

  • Hi apr301975,

    Please share your DDL, data sample population, and SQL statements in their entirety.

    Tuesday, November 12, 2019 8:09 PM
  • these are datetime2() format from SQL

    DateTime values are values with any format information, the client have to format it properly, 

    end out to csv using …. be format in Excel to proper format

    and CSV is a plain text file, also without any format information; Excel will format it as its like; no way to archive your requirements; and it is not an "inconsistency", it's way as it is with data.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, November 12, 2019 8:41 PM
  • Hi apr301975,

    Olf is right.

    Here are two solutions for your reference.

    Solution1 Please refer to this article and see if it overcomes that issue.

    Sending query results to Excel through e-mail

    Solution2 Firstly, use SSIS to generate excel files, then use parameter "@file_attachments" of system procedure "sp_send_dbmail " to send email with attached excel file.

    Export Data From SQL Server To Excel in SSIS

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 13, 2019 2:47 AM
    Moderator
  • I found the real problem.

    In one of the fields, there was a comma in "some" of the records. Consequentially, the field with a comma is split up as if the comma was the delimiter. This pushes all the fields to the right - and therefore the wrong value is used in my CONVERT formula.

    This CONVERT formula was used so csv file will have something more understandable like 10/29/2019 16:40 instead of something less understandable 39:10.0

    Thanks all.

    Wednesday, November 13, 2019 4:05 AM
  • I found the real problem.

    In one of the fields, there was a comma in "some" of the records. Consequentially, the field with a comma is split up as if the comma was the delimiter. This pushes all the fields to the right - and therefore the wrong value is used in my CONVERT formula.

    This CONVERT formula was used so csv file will have something more understandable like 10/29/2019 16:40 instead of something less understandable 39:10.0

    Thanks all.

    Thanks for your feedback.

    Well, since you have found the reason of the issue, please kindly close the thread by marking useful replies as answer.

    Thanks for your cooperation.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 14, 2019 6:57 AM
    Moderator