SELECT CONVERT (DATETIME, 0x00009E5D) varbinary (255) RRS feed

  • Question

  • Hi all,

    I am on a project that has migrated a varbinary(255) column from Sybase ase v12.5 running on 32 bit HP-UX to SQL Server 2008 r2 (10.50.1777) varbinary(255) on windows 2008r2 64 bit.

    On Sybase I run 'SELECT CONVERT (DATETIME, 0x00009E5D) ' returns '2010-12-31 00:00:00.0'.

    On Sql Server 2008r2 I run 'SELECT CONVERT (DATETIME, 0x00009E5D) ' returns '1900-01-01 00:02:15.137'.

    If I pad the word to the right 'SELECT CONVERT (DATETIME, 0x00009E5D00000000) ' returns '2010-12-31 00:00:00.000'

    I suspect this is due to large endian versus small endian binary notation at the operating systems but cannot seem to find it documented or any workarounds suggested. Any assistance with this would be greatly appreciated.



    p.s. I have attempted to cast the data like:

    'select convert (datetime,(CAST (0x00009E5D as BINARY(8))))' which returns  '2010-12-31 00:00:00.000' but fails to convert/cast properly when I select the column from the base table. I have qualified the query to return only rows sourced from the sybase migration. I am wondering if it has anything to do with the byte word size perhaps?


    • Edited by flht2000 Monday, January 23, 2012 10:37 PM
    Monday, January 23, 2012 9:18 PM