Search code examples
sqlsql-server-2014filemaker

Error converting data type DBTYPE_DBTIMESTAMP to datetime2

I have this code

SELECT
TRY_CONVERT(varchar(150), "PCR_Fecha")  AS "PCR_Fecha"
FROM OPENQUERY(EXTRACCION, 'SELECT * FROM EXTRACCION.Extraccion')

But i get the error 

Error converting data type DBTYPE_DBTIMESTAMP to datetime2.

I know there are wrong values in PCR_Fecha (like 40218:00:00 or 14mayo09) that's why I'm trying to convert them to varchar.

I can see the data using

SELECT * FROM OPENQUERY(EXTRACCION, 'SELECT CAST(PCR_Fecha AS varchar(26)) FROM EXTRACCION.Extraccion');

The linked server is from Filemaker and there PCR_Fecha is set as a date. Looking for an answer I found that I could define "dbtimestamp_rules=2" in connection string but I don't know how to add the rule.

Any other suggestions?

Solution

  • I found that I could change the data type from Filemaker and set it to text (it was date before) and that solves the problem.

    With sql it was 

    SELECT
Convert(NVARCHAR(26),PCR_Fecha)
FROM OPENQUERY(EXTRACCION, 'SELECT CAST(PCR_Fecha AS VARCHAR(26)) as PCR_Fecha FROM EXTRACCION.Extraccion')

    A cast and then convert, anyways thx :)