Avatar of dkilby
dkilby
Flag for Canada asked on

MS SQL + Send Email With Attachment

I am using msdb.dbo.sp_send_dbmail  to send an email with an attachment, I have tried attaching as an xls and csv, the problem is I have a datetime field and when i open the file the date column shows as a time, not formatted correctly, and not correct time.

Is there a way to format before sending ?

Example:

Created Date
------------
16:55.0
25:53.0
42:05.0
51:47.0
57:34.0

Should be

Created Date
------------
03/26/2016 17:16
03/26/2016 17:25
03/26/2016 19:42
03/26/2016 14:51
03/26/2016 14:57
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
dkilby

8/22/2022 - Mon
arnold

You are posting output that sheds no light on how or what generated it.
If you use CSV, enclosing using double quotes to note it is one column.
Your output formatting might be thrown off by ........
dkilby

ASKER
here is the query I am using

	DECLARE  @tab char(1) = CHAR(9)

		exec msdb.dbo.sp_send_dbmail 

		@profile_name = 'MailTest',

		@query = 'select orderid as [Order Number], createddate as [Created Date]
					from dbo.OrderInfo 
					where CREATEDDATE > cast(dateadd(day, -1, getDate()) as Date)
					order by orderid ' ,

		@subject = 'Orders for Yesterday',

		@attach_query_result_as_file = 1,

		@query_attachment_filename='Orders.xls',

		@query_result_separator=@tab,

		@query_result_no_padding=1

Open in new window

arnold

Your data is in tab separated values, the xls suffix ...
The separator

See if you enclose the created with double quotes
('"'.createddate.'"') as [created date]

If you look at the complete conversion, do you gave the 2016/03/26 in a column before created date?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
dkilby

ASKER
could you send me an example, I not sure what you mean with the double quotes as I cant put it in the query I have.
arnold

Try the following
	DECLARE  @tab char(1) = CHAR(9)

		exec msdb.dbo.sp_send_dbmail 

		@profile_name = 'MailTest',

		@query = 'select orderid as [Order Number], ' .'"'.'createddate'.'"'.' as [Created Date]
					from dbo.OrderInfo 
					where CREATEDDATE > cast(dateadd(day, -1, getDate()) as Date)
					order by orderid ' ,

		@subject = 'Orders for Yesterday',

		@attach_query_result_as_file = 1,

		@query_attachment_filename='Orders.tsv',

		@query_result_separator=@tab,

		@query_result_no_padding=1

Open in new window

dkilby

ASKER
i get this error

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '.'.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
arnold

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
dkilby

ASKER
Thanks for the help