1

I am trying to convert a string into a datetime in Sybase IQ. The string has this form: '20191211 11:49:00.565224 +0700'

I first try with Convert DATETIME: the following 2 queries fail:

SELECT CONVERT("DATETIME", '20191211 16:29:56.226560 +0000', 112) AS "TIME_dt"
SELECT CONVERT(DATETIME, '20191211 16:29:56.226560 +0000', 112) AS "TIME_dt"

However, when used in a query like

SELECT CONVERT("DATETIME", "TIME_", 112) AS "TIME_dt" FROM TABLE

and the column "TIME_" contains '20191211 16:29:56.226560 +0000' then I get the result: 2019-12-11 00:00:00 and it correctly transformed 20191211 to 2019-12-11

Next I try with SELECT CONVERT( DATETIMEOFFSET , '20191211 11:49:00.565224 +0700') and that works, but when I use it in a query like

SELECT CONVERT(DATETIMEOFFSET, "TIME_") AS "TIME_dt" FROM TABLE

I get an error with : 

[Code: 21, SQL State: QFA2A]  SQL Anywhere Error -1001030: Feature, Cast to Unknown TypeID (29) at line 1, is not supported. 
-- (dflib/df_Heap.cxx 6835)

What is going on?

thanks

Courvoisier
  • 904
  • 12
  • 26

1 Answers1

0

I had a look at https://www.w3schools.com/sql/func_sqlserver_convert.asp, basically, the third parameter is optional, in your case 112, it tells you exactly the format, that's why you are getting the formatting error, because it's missing 112. The other error has to do with how to pass the variable, what you are actually passing, a string or a date value.

Iria
  • 433
  • 1
  • 8
  • 20
  • I need it to read the offset. DATETIME while works, does not deal with the offset correctly. DATETIMEOFFSET deals with the offset, but fails when used in a table. – Courvoisier Dec 18 '19 at 11:20