MySQL 如何将十六进制转换为 varchar(datetime)？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4946292/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to cast the hexadecimal to varchar(datetime)?
提问by Zrot
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.
我有日期时间导出是“CAST（0x0000987C00000000 AS DateTime）”但是当我想把它恢复到日期时间时。它是一个空值。我怎样才能让它再次到日期时间。
回答by Martin Smith
That looks like the SQL Server datetimeformat. 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).
这看起来像 SQL Serverdatetime格式。在内部，这被存储为 2 个整数，前 4 个字节是自 1900 年 1 月 1 日以来的天数，第二个字节是自午夜以来的滴答数（每个滴答声为 1/300 秒）。
If you need to use this in MySQL you could do
如果你需要在 MySQL 中使用它，你可以这样做
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 solutionin splitting the binary data)
回答by Christopher Marshall
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).
并没有真正添加任何尚未说明的内容，但我使用它从上面的代码中创建了一个 MySql 函数。然后，我可以使用 RegEx 查找和替换（在 Notepad++ 中）将 CAST(0xblahblahblah AS DATETIME) 替换为 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);
回答by magoderre
This is the same select statement for PostgreSQL:
这是PostgreSQL的相同 select 语句：
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.
PostgreSQL bit(32) 值必须以 'x' 值而不是 0 开头。
回答by Fernando Reis Guimaraes
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,8), 16, 10) AS SIGNED) DAY + INTERVAL CAST(CONV(substr(HEX( 0x ),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),
回答by acdcjunior
Here's a Java program I did.
这是我做的一个 Java 程序。
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).
例如，考虑到SELECT CAST (0x00009CEF00A25634 as datetime)return 2009-12-30 09:51:03.000，程序会扫描文件CAST(0x00009CEF00A25634 AS DateTime)并将其替换为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.
我用它来将 SQL Server 生成的脚本转换为 H2 嵌入式数据库可以理解的内容。
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());
}
}
回答by Aleph
MSSQL hex code for date and dateTime are different.
日期和日期时间的 MSSQL 十六进制代码不同。
For date in fomate like 0x00000000 you can use this postgres function:
对于像 0x00000000 这样的日期，你可以使用这个 postgres 函数：
CREATE FUNCTION convertedata(text) RETURNS timestamp without time zone
as $$ SELECT '0001-01-01 00:00:00'::date + (('x'||
(regexp_replace(
substring(::text,3,8)::text,
'(\w\w)(\w\w)(\w\w)(\w\w)',
''))::text
)::bit(32)::int::text||'days')::interval $$
LANGUAGE SQL;
then try
然后尝试
select convertedata('0x0E360B00')
回答by Arthur
For those looking a solution in C#. For example when reading scripted database data.
对于那些在 C# 中寻找解决方案的人。例如在读取脚本数据库数据时。
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.
在这里，我使用了正则表达式，因为我的输入采用以下形式“CAST(0x0000A53E00E1A17B AS DateTime)”，但您可以使用 SubString() 或其他方法来获取 DateTime 字符串。
