如何改进RDB中以下SQL选择日期
我正在开发一个连接到旧版 RDB 数据库的 .Net WinForms 应用程序...
某些日期字段存储为整数（想象 2010-01-04 是整数 20100104）
在 .Net 世界中，我宁愿使用日期时间对象，我已经能够将整数转换为日期，只是它看起来很丑陋，占用很多行，很容易出错，我想知道是否有人能够改进它...非常感谢！
注意 - 我无法编辑数据库，因此不可能创建任何形式的“函数”...
这是我当前选择整数作为日期时间的方法：
select
CAST(
SUBSTRING(DATE_COL AS VARCHAR(8)) FROM 1 FOR 4) ||
'-' ||
SUBSTRING(CAST(DATE_COL) AS VARCHAR(8)) FROM 5 FOR 2) ||
'-' ||
SUBSTRING(CAST(DATE_COL) AS VARCHAR(8)) FROM 7 FOR 2) ||
' 00:00:00'
AS TIMESTAMP) AS DATE_COL
from MY_TABLE
where ...
I'm working on a .Net WinForms appliation that is connecting to a legacy RDB database...
Some of the fields for dates are stored as integers (imagine 2010-01-04 was the integer 20100104)
In the .Net world, I'd much rather work with datetime objects and I've been able to convert the integer into a date, just that it looks so ugly, takes up lots of lines, is prone to errors and I'm wondering if anyone would be able to improve it...Thanks heaps!
Note - I cannot edit the database so creating any form of "function" is out of the question...
Here's my current way of selecting the integer as a datetime:
select
CAST(
SUBSTRING(DATE_COL AS VARCHAR(8)) FROM 1 FOR 4) ||
'-' ||
SUBSTRING(CAST(DATE_COL) AS VARCHAR(8)) FROM 5 FOR 2) ||
'-' ||
SUBSTRING(CAST(DATE_COL) AS VARCHAR(8)) FROM 7 FOR 2) ||
' 00:00:00'
AS TIMESTAMP) AS DATE_COL
from MY_TABLE
where ...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
自从我不得不搞乱 Rdb 以来已经有一段时间了。我似乎记得有一种方法可以通过执行类似
WHERE 'YYYYMMDDHHNNSSTT' 是年-月-日-小时-分-秒分数格式的字符串来转换为 TIMESTAMP 数据类型。您可能需要在这里使用 DATE ANSI 而不是 TIMESTAMP - 就像我说的，已经有一段时间了。无论如何，以下方法可能会起作用：
这仍然很丑陋，但可能比所有子字符串更好。不管怎样，稍微玩一下，我相信你会明白的。
It's been a while since I had to mess with Rdb. I seem to recall that there was a way to convert to a TIMESTAMP datatype by doing something like
WHERE 'YYYYMMDDHHNNSSTT' was a character string in year-month-day-hour-min-sec-fraction format. You may need to use DATE ANSI here instead of TIMESTAMP - like I say, it's been a while. Regardless, the following might work:
which is still ugly but is perhaps better than all the substringing. Anyways, play with it a bit and I'm sure you'll get it.
在 Oracle 中，您可以在将
date_col转换为字符串后使用 TO_DATE：...或“YYYYDDMM”作为日期格式。
参考文献：
In Oracle, you can use the TO_DATE after you've cast the
date_colto a string:...or 'YYYYDDMM' for the date format.
References:
为什么不使用 LINQ 库？它非常好而且功能强大。
您正在运行的典型查询是什么（请提供高级伪代码）。
Why not use LINQ library? It is very nice and powerful.
What are the typical queries that you are running (high-level pseudocode please).