como converter o hexadecimal para varchar (datetime)?

Eu tenho a data e hora de exportação é “CAST (0x0000987C00000000 AS DateTime)”, mas quando eu quero recuperá-lo em datetime.It é um valor NULL. Como posso obtê-lo para datetime novamente.

Parece o formato de datetime e datetime do SQL Server. Internamente, isso é armazenado como 2 inteiros, com os primeiros 4 bytes sendo os dias desde 1º de janeiro de 1900 e o 2º sendo o número de ticks desde a meia-noite (cada tick sendo 1/300 de segundo).

Se você precisa usar isso no MySQL você pode fazer

 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 

Retorna

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

(Obrigado a Ted Hopp pela solução em dividir os dados binários)

Não estou realmente adicionando nada que não tenha sido declarado, mas eu usei isso para criar uma function MySql do código acima. Em seguida, posso usar um RegEx localizar e replace (no Notepad ++) para replace o CAST (0xblahblahblah AS DATETIME) com 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); 

Esta é a mesma instrução select para o 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); 

Os valores do bit PostgreSQL (32) devem começar com o valor ‘x’ em vez de 0.

Aqui está um programa Java que fiz.

O programa verifica o arquivo fornecido (altera o nome no código abaixo) para

 CAST(0x... AS DateTime) 

e substitui-os por seus respectivos

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

.

Por exemplo, considerando que SELECT CAST (0x00009CEF00A25634 as datetime) retorna 2009-12-30 09:51:03.000 , o programa verifica o arquivo para CAST(0x00009CEF00A25634 AS DateTime) e as substitui por CAST('2009-12-30 09:51:03.000' AS DateTime) .

Eu usei para converter um script gerado pelo SQL Server em algo que um database embutido H2 pudesse entender.

Embora funcionasse bem para mim, aconselho-o a verificá-lo (basta executar alguns dados de teste e ver) antes de usar os dados reais.

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

Usando o notepad ++ regex substitua

 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) 

Isso irá replace

 CAST(0x0000A26900F939A8 AS DateTime) 

para

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

O código hexadecimal MSSQL para date e dateTime é diferente.

Para data em fomate como 0x00000000 você pode usar esta function postgres:

 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; 

Então tente

 select convertedata('0x0E360B00') 

Para quem procura uma solução em C #. Por exemplo, ao ler dados de database com script.

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

Aqui eu usei regex desde que minha input está no seguinte formato “CAST (0x0000A53E00E1A17B AS DateTime)”, mas você pode usar SubString () ou qualquer outra coisa para obter a string DateTime.