How to pass hex to varchar (datetime)?

Question

I exported datetime: "CAST (0x0000987C00000000 AS DateTime)", but when I want to return it to datetime. This value is NULL. how can i get it in datetime again.

Answer 1

This is similar to the SQL Server datetime format. Internally, this is stored as 2 integers, and the first 4 bytes are the days from January 1, 1900, and the second is the number of ticks from midnight (each tick is 1/300 second).

If you need to use this in MySQL, you can 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 deciding on sharing binary data)

Answer 2

Not adding anything that was not indicated, but I used this to create the MySql function from the above code. Then I can use RegEx search and replace (in Notepad ++) to replace 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 values ​​(32) should begin with a value of "x" instead of 0.

Answer 4

Here was the Java program.

The program scans this file (change the name to the code below) to

 CAST(0x... AS DateTime) 

and replaces them with appropriate

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

.

For example, given that SELECT CAST (0x00009CEF00A25634 as datetime) returns 2009-12-30 09:51:03.000 , the program looks at 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 the generated SQL Server script to an embedded H2 database that I could understand.

Although this worked for me, I suggest you check it out (just run some test data and see) before using the 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) 

It 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 barcode for date and timeTime is different.

For a 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 for a solution in C #. For example, when reading data in a database.

  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); 

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