vba 如何将 DB2 日期时间字符串转换为 Excel 日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/166210/
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 convert a DB2 date-time string into an Excel Date
提问by Oli
I'll regularly get an extract from a DB/2 database with dates and timestaps formatted like this:
我会定期从 DB/2 数据库中提取日期和时间戳格式如下：
2002-01-15-00.00.00.000000
2008-01-05-12.36.05.190000
9999-12-31-24.00.00.000000
Is there an easier way to convert this into the Excel date format than decomposing with substrings?
有没有比用子字符串分解更简单的方法将其转换为 Excel 日期格式？
DB2date = DateValue(Left(a, 4) + "/" + Mid(a, 6, 2) + "/" + Mid(a, 9, 2))
thanks for your help!
感谢您的帮助！
采纳答案by tzot
It's not clear if you talk about formula functions or VBA functions.
不清楚您谈论的是公式函数还是 VBA 函数。
Formula functions
公式函数
Don't use the DateValue function, which expects a string; use the Date function, which expects numeric Year, Month, Day:
不要使用 DateValue 函数，它需要一个字符串；使用 Date 函数，它需要数字年、月、日：
=DATE(INT(LEFT(A1,4)),INT(MID(A1,6,2)),INT(MID(A1,9,2)))
assuming that the date-as-string is in A1.
假设日期字符串在 A1 中。
VBA functions
VBA 函数
Similar calculation as above, just use the DateSerialfunction instead:
与上面类似的计算，只需使用DateSerial函数：
dt= DateSerial(Int(Left$(dt$, 4), Int(Mid$(dt$, 6, 2)), Int(Mid$(dt$, 9, 2)))
回答by Philippe Grondier
in VBA, dateValue()can convert the first part of the string into a date:
在 VBA 中，dateValue()可以将字符串的第一部分转换为日期：
? dateValue("2002-01-15")
15/01/2002
So the right way to get it for you will be
因此，为您获取它的正确方法是
? dateValue(left("2002-01-15-00.00.00.000000",10))
This will always give you the right answer as long as DB2 always give you a "YYYY-MM-DD" date. The format of the result (dd/mm/yy, mm-ddd-yyyy, etc) will depend on the local settings of your computer/specific settings of your cell.
只要 DB2 始终为您提供“YYYY-MM-DD”日期，这将始终为您提供正确的答案。结果的格式（dd/mm/yy、mm-ddd-yyyy 等）将取决于您计算机的本地设置/单元的特定设置。
If you want to extract the "time" part of your string, there is this timeValue()function that will eventually make the job.
如果您想提取字符串的“时间”部分，则可以使用此timeValue()函数最终完成这项工作。
回答by blairxy
If you want to include the time information you have more to do; DateValue discards it.
TimeValue can evaluate the time part down to seconds, so you can add them:
如果您想包含时间信息，您还有更多工作要做；DateValue 丢弃它。
TimeValue 可以将时间部分计算为秒，因此您可以添加它们：
= DateValue(Mid(a, 1, 10)) + TimeValue(Mid(a, 12, 8))
But your sample data presents another problem: it has both time values "00.00.00" and "24.00.00".
The second one gags the TimeValue function, so you will need to code for the special case.
但是您的样本数据存在另一个问题：它同时具有时间值“00.00.00”和“24.00.00”。
第二个阻塞了 TimeValue 函数，因此您需要为特殊情况编写代码。
回答by Matthew Scharley
I'm sure you could cook something up with Regex's if you really cared to. It wouldn't be any 'better' though, probably worse.
如果你真的愿意，我相信你可以用 Regex 做一些事情。不过，它不会有任何“更好”，甚至可能更糟。
If you'll forgive a bit of C# (I havn't touched VB in years, so I don't know the function calls anymore) you could also do:
如果你能原谅一些 C#（我已经很多年没有接触过 VB，所以我不再知道函数调用了）你也可以这样做：
DB2string = "2002-01-15-00.00.00.000000";
DB2date = DateValue(DB2string.SubString(0, 10).Replace('-', '/'));
But again, you're not really gaining anything. Can you give an example of where your current code would break?
但同样，你并没有真正获得任何东西。你能举一个例子说明你当前的代码会在哪里中断吗？
