CSV date column not formatted through sp_send_Dbmail

• EasyBoy

  SSCertifiable

  Points: 5301

  More actions

  June 22, 2017 at 9:18 am

  #402206

  Hello,
  I am sending a CSV file through sp_sebd_dbmail which contains couple of date datatype. But, when i look at the CSV file date is not formatted. Is there any way i can get the formatted date?
  Thanks for your help in advance.
  Query:

  declare @qry varchar(8000)
  declare @column1name varchar(50)

  SET @Column1Name = '[sep=,' + CHAR(13) + CHAR(10) + 'BatchID]'

  -- Create the query, concatenating the column name as an alias
  select @qry='set nocount on;select BatchID ' + @column1name +
       ' ,UserName,FQFilename,
              DateDownloaded,DateCreated,Type,IsLocked,DateInitiated
              FROM table
          WHERE DateDownloaded IS NULL
          ORDER BY DateCreated DESC'

  -- Send the e-mail with the query results in attach
  exec msdb.dbo.sp_send_dbmail
  --@profile_name = 'SRVSQLMON',
   @Recipients='xxx@xyz.com',
  @query=@qry,
  @subject='Test Result',
  @attach_query_result_as_file = 1,
  @query_attachment_filename = 'result.csv',
  @query_result_separator=',',@query_result_width =32767,
  @query_result_no_padding=1
  

• EasyBoy

  SSCertifiable

  Points: 5301

  More actions

  June 22, 2017 at 9:44 am

  #1947798

  Never mind. I found the solution.
  Updated the query using covnert function:
  
select @qry='set nocount on;select BatchID ' + @column1name + 
     ' ,UserName,FQFilename,
             CONVERT(VARCHAR(20),DateDownloaded,20) as DateDownloaded,
            CONVERT(VARCHAR(20),DateCreated,20) As DateCreated,Type,IsLocked,
            CONVERT(VARCHAR(20),DateInitiated,20) as DateInitiated 
            FROM table
        WHERE DateDownloaded IS NULL 
        ORDER BY DateCreated DESC'