6

I need compare property datetime2 that one database with property datetime that other database because I don't find method for convert datetime to datetime2.

I test this sentence:

select CAST(FechaAlta AS datetime) from tutors

but return this error message:

The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

ughai
  • 9,830
  • 3
  • 29
  • 47
Raquel Rodriguez Gonzalez
  • 57
  • 1
  • 4

2 Answers2

4

The datetime2 data type has a wider range than datetime - datetime2 can hold any date between 0001-01-01 and 9999-12-31, whereas datetime starts at 01/01/1753 (there are obscure reasons for this involving the 4th Earl of Chesterfield that are too arcane to go into at this juncture).

If you want to convert your datetime2 to a datetime, you have to handle the out-of-range values, for instance:

SELECT CASE WHEN FechaAlta < '1753-01-01' THEN NULL ELSE CAST(FechaAlta AS DateTime) END AS FechaAlta_As_DateTime FROM Tutors
Ed B
  • 785
  • 4
  • 9
-2

First get datetime2 and datetime in the same datetime format. Assuming they both are of date datatype and you are working on Oracle

TO_DATE(TO_CHAR(DATETIME2,'YYYYMMDD HH24:MI:SS')) = TO_DATE(TO_CHAR(DATETIME,'YYYYMMDD HH24:MI:SS'))
TFrost
  • 769
  • 2
  • 12
  • 31
Murali
  • 67
  • 1
  • 1
  • 2