1

Many have come across the fact that SQL Server 2005 doesn't support datetime2. I was wondering if I can add it as a custom datatype instead.

I created a custom type with the name datetime2 so that's done.

Now I need to set the min date value, but is that even possible, since the custom type is based on the datetime type?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ruben Verschueren
  • 822
  • 13
  • 28

2 Answers2

3

Short answer: No you can't.

Furthermore, DATETIME2 has additional properties, regarding fractional precision. I'd highly recommend any SQL Server DBA to migrate their server to at least 2008, to open up the rich features that are not available in 2005. I don't see any advantage to using something that is largely deprecated and over 10 years old. Especially since you're resorting to creating UDTs that will potentially create all kinds of RDBMS havoc in the future.

John Bell
  • 2,350
  • 1
  • 14
  • 23
  • I agree, sadly I'm working as a consultant and don't have a lot of control over the database. A new project is due to start to remake this application... but for the moment I'm stuck with SQL Server 2005. – Ruben Verschueren May 08 '15 at 12:00
  • Out of interest, why do you want to go past 1753-01-01? I didn't know Black Beard had an Internet Banking account ;) – John Bell May 08 '15 at 12:12
  • because the application searches an archive of orphans abandoned by their parents from that period of time. It displays scans from a book which contains all the data, where and when they were found etc – Ruben Verschueren May 08 '15 at 12:54
  • Well don't I feel insensitive and silly now. As the consultant on that project, I would get them to push that architecture update pronto. Sounds like they need it quite badly. – John Bell May 08 '15 at 13:02
  • I used an ugly fix.. the date's are now varchars. So the stored procedure for the search contains some conversion magic between date and varchar. It isn't pretty, but it works. – Ruben Verschueren May 13 '15 at 09:40
0

To get over this I changed the datatypes in the database to varchar. next I modified the stored procedure to do the conversion between the date entered in the search form and the values in the database. It's an ugly solution and I have to 'manually' compare year, month and day values, but it works in the end with a minimal effort. Customer happy, developer... on the fence :-)

Ruben Verschueren
  • 822
  • 13
  • 28