1

I have a date field (based on DateTime in .NET) in Crystal Reports. I go through a complex format dialog, set the day to be 0n, the month to me 0n, and the year to be None, desiring a date representation of dd/MM. What I get is dd/MM hh:mm:ssAM.

What do I have to do to get rid of the time portion? It is overwriting other columns in the report making it unreadable?

I am using SQL Server 2012 Enterprise.

ProfK
  • 49,207
  • 121
  • 399
  • 775
  • Hi @ProfK, you have 3 possible answers, but haven't commented or green checked any of them. Why don't you try a few and let the helpful responder know if it worked or not? – PowerUser Aug 15 '13 at 17:08
  • What field type is displayed in Crystal Reports? – craig Aug 15 '13 at 19:45
  • What database (e.g. Oracle) are you using? – craig Aug 15 '13 at 19:45
  • @craig I'm using SQL 2012 Enterprise, and the field in the report is an `IFieldObject`, but is named `Date1`. If it looks like a date... It has hosts of date related properties. – ProfK Aug 16 '13 at 06:14
  • In SQL Server, the field's data type is `Date`? Does Crystal Reports also indicate that the field is a `Date`? If you browse the field in CR, you'll be able to determine its data type. – craig Aug 16 '13 at 14:22
  • @craig Yes, if I browse the field data for the field it says type Date, but I can't find anywhere else the type of the field is displayed, but in the DB it is type **`Date`**, vs. `DateTime` or `SmallDateTime`. – ProfK Aug 17 '13 at 05:01
  • Can you truncate the date field to just date? In Oracle, the function is trunc, but I guess it is a little more complicated in SQL Server: http://stackoverflow.com/questions/923295/how-can-i-truncate-a-datetime-in-sql-server – Sun Feb 04 '16 at 17:21

4 Answers4

4

Use formula 

date(datetime field)
Siva
  • 9,043
  • 12
  • 40
  • 63
4

Right click the field and select "Format Field...":

enter image description here

Click "Customize...":

enter image description here

Select "Date" from the "Order:" picklist.

Select the "Date" tab:

enter image description here

Set a 2-digit month, a 2-digit day, and 'None' for the year.

craig
  • 25,664
  • 27
  • 119
  • 205
  • When Ii right click the field, I get no "Format Field", only "Format Object", and when I click that, the Format Editor only has "Date" and not "Date and Time". It sets the day and month perfectly, but Crystal, in its infinite wisdom, decides the time `12:00:00AM` must be appended. – ProfK Aug 15 '13 at 19:40
  • What happens if you were to create a formula to force the field to be a date: `Date({table.field})`? Does this truncate the time portion and allow you to format the field as desired? – craig Aug 16 '13 at 14:25
  • Does this answer just format the DateTime so time doesn't appear? In formulas, calculated a truncated date will be seen as midnight versus a DateTime with their respective time? – Sun Feb 04 '16 at 17:19
0

Call the field to a formula field. write a formula like this

todate(field_name)

now drag the formula field and format it, there will be no time there!.

Waqar
  • 216
  • 1
  • 8
0

Make sure that your Crystal Reports Details section previews your datetime column as a 'string' when focus on it, if so, go back to your table structure, and change your table's column datatype to smalldatetime, then in Crystal Reports, Right Click on Database Field in the File Explorer, then Click on Verify Database, then Ok, after verification of your database data to up to date, Right Click on you desired column and choice Format Object, now, Format Editor Window should appear, then select the Format you earned.

Source: My Experience

Abdisamad Khalif
  • 765
  • 2
  • 7
  • 19