blocks|key|3017538|text|这看起来像SQL+Server+datetime格式。在内部，这被存储为2个整数，前4个字节是自1900年1月1日以来的天数，第2个字节是自午夜以来的滴答数(每个滴答是1/300秒)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3017539|如果你需要在MySQL中使用它，你可以这样做|3017540|SELECT+
++++++CAST(
++++++++++'1900-01-01+00:00:00'+%2B+
++++++++++INTERVAL+CAST(CONV(substr(HEX(BinaryData),1,8),+16,+10)++AS+SIGNED)+DAY+%2B
++++++++++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|code-block|syntax|javascript|3017541|返回|3017542|converted_datetime
--------------------------
2006-11-17+00:00:00
2011-02-09+18:52:34.286667|3017543|(感谢the+solution的Ted+Hopp拆分二进制数据)|3017544|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/4948062/working-with-binary-data-in-mysql/4948307#4948307^0|G|8|0|0|0|0|0|3|C|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|13|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|14|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|15|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|16|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|17|8|@]|D|@$9|18|A|19|1|1A]]|E|$]]|$1|S|3|-4|5|6|7|1B|8|@]|D|@]|E|$]]]|T|$U|$5|V|W|X|E|$Y|Z]]]]

That looks like the SQL Server <code>datetime</code> 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 

<pre><code>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
</code></pre>

Returns

<pre><code>converted_datetime
--------------------------
2006-11-17 00:00:00
2011-02-09 18:52:34.286667
</code></pre>

(Thanks to Ted Hopp for <a href="https://stackoverflow.com/questions/4948062/working-with-binary-data-in-mysql/4948307#4948307">the solution</a> in splitting the binary data)

blocks|key|950224|text|实际上并没有添加任何未声明的内容，但我使用它从上面的代码创建了一个MySql函数。然后，我可以使用RegEx查找和替换(在Notepad%2B%2B中)将转换(0xblahblahblah作为DATETIME)替换为sp_ConvertSQLServerDate(0xblahblahblah)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|950225|create+function+sp_ConvertSQLServerDate(dttm+binary(16))
returns+datetime
return+CAST(
++++++'1900-01-01+00:00:00'+%2B+
++++++INTERVAL+CAST(CONV(substr(HEX(dttm),1,8),+16,+10)++AS+SIGNED)+DAY+%2B
++++++INTERVAL+CAST(CONV(substr(HEX(dttm),9,8),+16,+10)++AS+SIGNED)*+10000/3+MICROSECOND
AS+DATETIME);|code-block|syntax|javascript|950226|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>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);
</code></pre>

blocks|key|1824722|text|这是我做的一个Java程序。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1824723|程序扫描给定的文件(在下面的代码中更改名称)|1824724|CAST(0x...+AS+DateTime)|code-block|syntax|javascript|1824725|并将它们替换为各自的|1824726|CAST('yyyy-MM-dd+HH:mm:ss.SSS'+AS+DateTime)|1824727|。|1824728|例如，考虑到SELECT+CAST+(0x00009CEF00A25634+as+datetime)返回2009-12-30+09:51:03.000，程序将扫描文件中的CAST(0x00009CEF00A25634+AS+DateTime)并将其替换为CAST('2009-12-30+09:51:03.000'+AS+DateTime)。|offset|length|style|CODE|1824729|我使用它将SQL+Server生成的脚本转换为H2嵌入式数据库可以理解的内容。|1824730|尽管它对我来说工作得很好，但我建议你在使用实际数据之前检查它(只需在一些测试数据上运行并查看)。|1824731|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,+"'"%2BsqlServerHexToSqlDate(s)%2B"'");
++++++++}
++++++++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()+%2B+millisToAdd);

++++++++return+sdf.format(convertedCal.getTime());
++++}
}|1824732|entityMap^0|0|0|0|0|0|0|6|18|1G|N|2D|10|3J|17|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|13|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|14|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|15|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|16|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|17|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|18|8|@$Q|19|R|1A|S|T]|$Q|1B|R|1C|S|T]|$Q|1D|R|1E|S|T]|$Q|1F|R|1G|S|T]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1H|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1I|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|F|7|1J|8|@]|9|@]|A|$G|H]]|$1|10|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]]|11|$]]

Here's a Java program I did.

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

<pre><code>CAST(0x... AS DateTime)
</code></pre>

and replaces them with their respective

<pre><code>CAST('yyyy-MM-dd HH:mm:ss.SSS' AS DateTime)
</code></pre>

.

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

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.

<pre><code>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") &gt; -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());
 }
}
</code></pre>

blocks|key|950339|text|这与PostgreSQL的select语句相同|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|950340|SELECT+'1900-01-01+00:00:00'::date+%2B
++++(('x'%7C%7Csubstring(x::text,3,8))::bit(32)::int::text%7C%7C'days')::interval+%2B
++++((('x'%7C%7Csubstring(x::text,11,8))::bit(32)::int+/300)::text%7C%7C'+seconds')::interval
FROM+(VALUES+
++++('0x00009fff00e24076'),
++++('0x00009ff10072d366'),
++++('0x00009ff10072ce3a'),
++++('0x00009ff10072c5e2'),
++++('0x00009ff10072bc3c'))++as+x(x);|code-block|syntax|javascript|950341|PostgreSQL位(32)值必须以'x‘值开头，而不是0。|950342|entityMap^0|2|A|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

This is the same select statement for PostgreSQL:

<pre><code>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);
</code></pre>

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

blocks|key|3017690|text|使用notepad%2B%2B正则表达式替换|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3017691|cast[(]0x([0-9A-F]{16})+As+DateTime[)]

CAST('1900-01-01+00:00:00'+%2B+INTERVAL+CAST(CONV(substr(HEX(+0x\1+),1,8),+16,+10)++AS+SIGNED)+DAY+%2B+INTERVAL+CAST(CONV(substr(HEX(+0x\1+),9,8),+16,+10)++AS+SIGNED)*+10000/3+MICROSECOND+AS+DATETIME)|code-block|syntax|javascript|3017692|这将替换|3017693|CAST(0x0000A26900F939A8+AS+DateTime)|3017694|至|3017695|CAST('1900-01-01+00:00:00'+%2B+INTERVAL+CAST(CONV(substr(HEX(+0x0000A26900F939A8+),1,8),+16,+10)++AS+SIGNED)+DAY+%2B+INTERVAL+CAST(CONV(substr(HEX(+0x0000A26900F939A8+),9,8),+16,+10)++AS+SIGNED)*+10000/3+MICROSECOND+AS+DATETIME),+|3017696|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Using notepad++ regex replace

<pre><code>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)
</code></pre>

This will replace 

<pre><code>CAST(0x0000A26900F939A8 AS DateTime)
</code></pre>

to 

<pre><code>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), 
</code></pre>

blocks|key|4617318|text|日期和dateTime的MSSQL十六进制代码不同。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4617319|对于格式为0x00000000的日期，您可以使用此postgres函数：|4617320|CREATE+FUNCTION+convertedata(text)+RETURNS+timestamp+without+time+zone
as+$$+SELECT+'0001-01-01+00:00:00'::date+%2B+(('x'%7C%7C
(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%7C%7C'days')::interval+$$
LANGUAGE+SQL;|code-block|syntax|javascript|4617321|然后试一试|4617322|select+convertedata('0x0E360B00')|4617323|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

MSSQL hex code for date and dateTime are different.

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

<pre><code>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;
</code></pre>

then try

<pre><code>select convertedata('0x0E360B00')
</code></pre>

blocks|key|3017820|text|对于那些在C#中寻找解决方案的人。例如，当读取脚本数据库数据时。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3017821|++++++++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"+%2B+m.Groups[1].Value,+16);
++++++++int+t+=+System.Convert.ToInt32("0x"+%2B+m.Groups[2].Value,+16);

++++++++DateTime+converted+=+new+DateTime(1900,+1,+1).AddDays(d).AddSeconds(t/300);|code-block|syntax|javascript|3017822|在这里，我使用了正则表达式，因为我的输入格式如下："CAST(0x0000A53E00E1A17B+AS+DateTime)"，但是您可以使用SubString()或其他方法来获取DateTime字符串。|3017823|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

<pre><code> 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);
</code></pre>

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.

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.

how to cast the hexadecimal to varchar(datetime)?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我让datetime导出是"CAST(0x0000987C00000000 AS DateTime)“，但是当我想将它返回到datetime.It时，它是一个空值。我怎样才能让它再次出现在datetime上。

问如何将十六进制转换为varchar(datetime)？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将十六进制转换为varchar(datetime)？EN