Alternatively, if you want to use CONVERT() in your query, then convert to varchar, and set the format to 101 (or similar). As long as you are using DATETIME, then there is a time component, which you need to remove somehow (either in SSRS display, or by changing to a type that doesn't have time).
CONVERT() syntax (inc formats) http://msdn.microsoft.com/en-us/library/aa226054(v=sql.80).aspx Cheers Ken From: Jeff Steward [mailto:[email protected]] Sent: Friday, 2 November 2012 10:47 AM To: NT System Admin Issues Subject: Re: OT: SSRS question Right click the text box in which you are displaying the date/time field. Select Text Box Properties. Click Number. Chose Date from the Category. Select the format type you want. Alternatively, type a lowercase d in the Format property of the Text Box to get a mm/dd/yyyy display. -Jeff On Thu, Nov 1, 2012 at 3:32 PM, Christopher Bodnar <[email protected]<mailto:[email protected]>> wrote: Any SSRS guys out there that might be able to help me with this? It's 2008 R2. Creating a report that uses a log file as the data source. Using an OLEDB connection string and a SCHEMA.INI file to set a custom delimiter: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\10.x.x.x\Data\;Extended Properties="text;HDR=Yes;Format=Delimited(#)" Everything is working perfectly, except for one nagging detail. I've got a field in the log file "DATE" that I'm getting back as DateTime in the report: 11/01/2012 12:00:00 AM I want to truncate this to just date. 11/01/2012 I've tried almost every combination I can think of and can't get it to give me just the date. Tried combinations of the following: Format FormatDateTime Convert Cast CDate Here is info on the dataset: query: select * FROM sample.txt Filter Expression: =Fields!Date.Value Expression for value: =DateAdd(DateInterval.Day , -7, Today) Any help is much appreciated. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected] with the body: unsubscribe ntsysadmin
