2

I have a vb.net project that I converted from Access to SQL Server.

In Access, all of my date/time fields were saved asDateTime, obviously. However, here is my problem:

When I migrated the database over to SQL Server 2008, it is now saving them as datetime2 columns, rather than datetime. This is causing problems in Crystal Reports, as it converts these to strings, not datetime.

I have hundreds of records in the table, so I can't delete it and re-design it, so is there any other way I can change the columns from datetime2 to datetime, WITHOUT losing the data I have saved in the table?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
David
  • 2,298
  • 6
  • 22
  • 56
  • 3
    Fix the report, don't break the database. This is 100% a bug in Crystal Reports, or a problem in the report itself. Although I find it hard to believe that Crystal Reports can't handle a data type that exists for 8 years. Are you using a very old CR version perhaps? – Panagiotis Kanavos Jul 26 '16 at 15:19
  • 1
    @PanagiotisKanavos I need them as datetime fields in the report, though. As they are, it converts them to strings, which causes problems to formulas and formatting. – David Jul 26 '16 at 15:20
  • 1
    As I said, I find it very, very, very hard to believe that any current CR version fails to understand `datetime2`. It may need a change to the *format* string, or a change to the field type, but that should be it. What version are you using? – Panagiotis Kanavos Jul 26 '16 at 15:21
  • @PanagiotisKanavos I'm using CR version 2008. At the moment, it displays both the date and time from the field, but I need to only show the date on my report, but the format field doesn't allow this, that I've found, and I'm not sure how I'd do this using a formula – David Jul 26 '16 at 15:23
  • 1
    http://stackoverflow.com/questions/15555732/convert-datetime2-to-datetime-from-stored-procedure-executed-result – Jim Hewitt Jul 26 '16 at 15:24
  • @JimHewitt Lovely, thank you, problem solved!! – David Jul 26 '16 at 15:34
  • Possible duplicate of [How to convert DateTime object to string in Crystal Reports](http://stackoverflow.com/questions/20548852/how-to-convert-datetime-object-to-string-in-crystal-reports) – Panagiotis Kanavos Jul 26 '16 at 15:37
  • @David actually not. That answer is plain wrong. It depends on an implicit conversion to a string. You *can* format a date directly using `ToText`. An actual solution is shown in [this question](http://stackoverflow.com/questions/20548852/how-to-convert-datetime-object-to-string-in-crystal-reports) – Panagiotis Kanavos Jul 26 '16 at 15:37
  • 1
    @PanagiotisKanavos The above has worked for me, it just displays the date. What difference would it make to the report if I used the `ToText` formula? – David Jul 26 '16 at 15:41
  • That solution worked by accident. Using `ToText(myField,"dd-MM-YYYY")` or `ToText(myField,"YYYY-MM-dd")` leaves nothing to chance and allows you to use exactly the format you want. It's also crystal clear what the formula does and allows you to easily change the format. For example, how would you generate month *names* by string splitting? With ToText you'd write `ToText(myField,"dd-MMM-YYYY")` – Panagiotis Kanavos Jul 26 '16 at 15:43
  • Is your Crystal Report connecting directly to the SQL Server database? Is it using an ODBC connection? If so, which SQL Server ODBC driver is it using? ...`SQL Server`? ...`SQL Server Native Client`? ...? – Gord Thompson Jul 26 '16 at 16:22
  • I'd change the datetime2 back to datetime in the converted table - just open the table design, change the type, and save. I'd probably use smalldatetime. – rheitzman Jul 26 '16 at 17:05
  • @rheitzman I tried this, it doesn't let me save it, so I had to use CR formula instead – David Jul 27 '16 at 08:20
  • @GordThompson Hi, the CR is connecting directly to SQL Server – David Jul 27 '16 at 08:21
  • @PanagiotisKanavos using this code `DateString := ToText({tblContracts.DateStart}, "dd-MM-yyyy");` didn't work, it gave me an error of "The remaining text does not appear to be part of the formula"? – David Jul 27 '16 at 08:25
  • I forgot that the change data type trick didn't work - I consider this a bug. I'll probably be using the Microsoft SQL Server Migration Assistant for Access more as it allows for changing data type. It doesn't support field/table name change which is surprising. – rheitzman Jul 29 '16 at 15:19

3 Answers3

2

The migration assistant for Access will by default up-size to the newer datetime2 columns.

If you continue to use the default (legacy) SQL driver with Access or say crystal reports as the client to SQL server, then all such columns will be returned as STRINGS when using the older driver.

The two solutions I would suggest thus are:

One: Re-migrate the data and tables again, but BEFORE you up-size the data you change the field mappings in SSMA to use the older datetime format as opposed to datetime2.

Two: Don’t use the legacy ODBC driver. Use the native 10 or 11 driver, and thus the datetime2 columns WILL be returned as actual date time columns with the ODBC driver. The only downside of this suggesting is these native SQL 10 or 11 drivers are NOT installed by default – so people often use the older legacy drivers for ease of distribution – especially for Access front ends that connect to SQL server. And to be fair, the older legacy SQL driver is VERY old - so it is in general recommend to use the native 11 driver these days.

While using native 10 or 11 drivers will fix this issue, those drivers will have to be installed on each workstation. So using the new SQL drivers with crystal will fix this issue.

It would be INCREDIBLE silly to have to start writing custom quires and views SQL server side to “convert” the date/time2 columns to the older datetime format in each query – I would strong ignore such suggestions being floated here. CR can work and use the datetime2 - but not when using the older SQL driver - simply adopt the native drivers and this issue goes away.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
0

The fix to this was to use queries in SQL Server.

  • ALTER TABLE table_name ADD new_column datetime
  • SET new_column = old_column
  • ALTER TABLE DROP old_column

This may also include the need to edit primary keys etc and relationships but these can just be added straight back in again. Finally, rename the new column with the old columns name, and move it into the correct location.

David
  • 2,298
  • 6
  • 22
  • 56
-1

A while back I had an issue with exporting dates from Access into an importable format for SQL Server or anything else. Access does not export them into a proper format that SQL can import, so I had to write a parser/converter to get the dates into an importable format.

CatCatMcMeows
  • 84
  • 6