how to cast the hexadecimal to varchar(datetime)?

Question

I have the datetime exporting is "CAST(0x0000987C00000000 AS DateTime)" but when I want to get it back into datetime.It is a NULL value. how can i get it to datetime again.

Answer 1

That looks like the SQL Server datetime format. Internally this is stored as 2 integers with the first 4 bytes being the days since 1st jan 1900 and the 2nd being the number of ticks since midnight (each tick being 1/300 of a second).

If you need to use this in MySQL you could do

SELECT 
      CAST(
          '1900-01-01 00:00:00' + 
          INTERVAL CAST(CONV(substr(HEX(BinaryData),1,8), 16, 10)  AS SIGNED) DAY +
          INTERVAL CAST(CONV(substr(HEX(BinaryData),9,8), 16, 10)  AS SIGNED)* 10000/3 MICROSECOND
      AS DATETIME) AS converted_datetime
FROM
(
SELECT 0x0000987C00000000 AS BinaryData
UNION ALL
SELECT 0x00009E85013711EE AS BinaryData
) d

Returns

converted_datetime
--------------------------
2006-11-17 00:00:00
2011-02-09 18:52:34.286667

(Thanks to Ted Hopp for the solution in splitting the binary data)

Answer 2

Not really adding anything that hasn't been stated but I used this to create a MySql function from the above code. I can then use a RegEx find and replace (in Notepad++) to replace the CAST(0xblahblahblah AS DATETIME) with sp_ConvertSQLServerDate(0xblahblahblah).

create function sp_ConvertSQLServerDate(dttm binary(16))
returns datetime
return CAST(
      '1900-01-01 00:00:00' + 
      INTERVAL CAST(CONV(substr(HEX(dttm),1,8), 16, 10)  AS SIGNED) DAY +
      INTERVAL CAST(CONV(substr(HEX(dttm),9,8), 16, 10)  AS SIGNED)* 10000/3 MICROSECOND
AS DATETIME);

Answer 3

This is the same select statement for PostgreSQL:

SELECT '1900-01-01 00:00:00'::date +
    (('x'||substring(x::text,3,8))::bit(32)::int::text||'days')::interval +
    ((('x'||substring(x::text,11,8))::bit(32)::int /300)::text||' seconds')::interval
FROM (VALUES 
    ('0x00009fff00e24076'),
    ('0x00009ff10072d366'),
    ('0x00009ff10072ce3a'),
    ('0x00009ff10072c5e2'),
    ('0x00009ff10072bc3c'))  as x(x);

PostgreSQL bit(32) values have to start with 'x' value instead of 0.

Answer 4

Here's a Java program I did.

The the program scans the given file (change de name on the code below) for

CAST(0x... AS DateTime)

and replaces them with their respective

CAST('yyyy-MM-dd HH:mm:ss.SSS' AS DateTime)

.

For instance, considering that SELECT CAST (0x00009CEF00A25634 as datetime) returns 2009-12-30 09:51:03.000, the program scans the file for CAST(0x00009CEF00A25634 AS DateTime) and replaces them with CAST('2009-12-30 09:51:03.000' AS DateTime).

I used it to convert a SQL Server generated script into something a H2 embedded database could understand.

Altough it worked fine for me, I advise you check it (just run on some test data and see) before using on actual data.

import java.io.*;
import java.text.*;
import java.util.*;
import java.util.regex.*;

public class ReplaceHexDate {

    public static void main(String[] args) throws Exception {
        String inputFile = "C:/input.sql";
        String inputEncoding = "UTF-8";
        String outputFile = "C:/input-replaced.sql";
        String outputEncoding = "UTF-8";

        BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(inputFile), inputEncoding));
        BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outputFile), outputEncoding));

        String line;
        while ((line = br.readLine()) != null) {
            if (line.indexOf("CAST(0x") > -1) {
                bw.write(replaceHexWithDate(line));
            } else {
                bw.write(line);
            }
            bw.newLine();
        }
        br.close();
        bw.flush();
        bw.close();
    }

    private static String replaceHexWithDate(String sqlLine) throws ParseException {
        Pattern castPattern = Pattern.compile("(CAST\\()(0x[A-Fa-f0-9]{16})( AS DateTime\\))");
        Matcher m = castPattern.matcher(sqlLine);
        while (m.find()) {
            String s = m.group(2);
            sqlLine = sqlLine.replace(s, "'"+sqlServerHexToSqlDate(s)+"'");
        }
        return sqlLine;
    }

    public static String sqlServerHexToSqlDate(String hexString) throws ParseException {
        String hexNumber = hexString.substring(2); // removes the leading 0x
        String dateHex = hexNumber.substring(0, 8);
        String timeHex = hexNumber.substring(8, 16);

        long daysToAdd = Long.parseLong(dateHex, 16);
        long millisToAdd = (long) (Long.parseLong(timeHex, 16) *10/3);

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");

        Calendar startingCal = Calendar.getInstance();
        String startingDate = "1900-01-01 00:00:00.000";
        startingCal.setTime(sdf.parse(startingDate));

        Calendar convertedCal = Calendar.getInstance();
        convertedCal.setTime(sdf.parse(startingDate));
        convertedCal.add(Calendar.DATE, (int) daysToAdd);
        convertedCal.setTimeInMillis(convertedCal.getTimeInMillis() + millisToAdd);

        return sdf.format(convertedCal.getTime());
    }
}

Answer 5

Using notepad++ regex replace

cast[(]0x([0-9A-F]{16}) As DateTime[)]

CAST('1900-01-01 00:00:00' + INTERVAL CAST(CONV(substr(HEX( 0x\1 ),1,8), 16, 10)  AS SIGNED) DAY + INTERVAL CAST(CONV(substr(HEX( 0x\1 ),9,8), 16, 10)  AS SIGNED)* 10000/3 MICROSECOND AS DATETIME)

This will replace

CAST(0x0000A26900F939A8 AS DateTime)

to

CAST('1900-01-01 00:00:00' + INTERVAL CAST(CONV(substr(HEX( 0x0000A26900F939A8 ),1,8), 16, 10)  AS SIGNED) DAY + INTERVAL CAST(CONV(substr(HEX( 0x0000A26900F939A8 ),9,8), 16, 10)  AS SIGNED)* 10000/3 MICROSECOND AS DATETIME), 

Answer 6

MSSQL hex code for date and dateTime are different.

For date in fomate like 0x00000000 you can use this postgres function:

CREATE FUNCTION convertedata(text) RETURNS timestamp without time zone
as $$ SELECT '0001-01-01 00:00:00'::date + (('x'||
(regexp_replace(
substring($1::text,3,8)::text,
 '(\w\w)(\w\w)(\w\w)(\w\w)',
 '\4\3\2\1'))::text
)::bit(32)::int::text||'days')::interval $$
LANGUAGE SQL;

then try

select convertedata('0x0E360B00')

Answer 7

For those looking a solution in C#. For example when reading scripted database data.

        string pattern = @"CAST\(0x(\w{8})(\w{8}) AS DateTime\)";
        Regex r = new Regex(pattern);
        Match m = r.Match(hex);            

        int d = System.Convert.ToInt32("0x" + m.Groups[1].Value, 16);
        int t = System.Convert.ToInt32("0x" + m.Groups[2].Value, 16);

        DateTime converted = new DateTime(1900, 1, 1).AddDays(d).AddSeconds(t/300);

Here I used regex since my input is in the following form "CAST(0x0000A53E00E1A17B AS DateTime)", but you can use SubString() or whatever to get the DateTime string.