在 DB2 中将字符串转换为日期 [英] Converting a string to a date in DB2

问题描述

我第一次使用 DB2 数据库.

I am working with a DB2 database for the first time.

我正在尝试处理 DB2 日期，但数据作为字符串存储在 DB2 数据库中.

I am trying to work with DB2 dates, but the data is stored as a string in the DB2 database.

我想将此日期字符串转换为实际日期，最好是下车时间，因为我想要 2011 年 1 月 1 日至 2011 年 1 月 26 日之间的所有交易.

I want to convert this date-string into an actual date, preferably dropping off time because I want all transactions between 1 Jan 2011 and 26 Jan 2011.

所以本质上，我想要 DB2 魔法中的这个 MS SQL 语句......

So essentially, I want this MS SQL statement in DB2 magic...

CONVERT(datetime,SETTLEMENTDATE.VALUE,103)

对于背景，我已经到了

CAST(SETTLEMENTDATE.VALUE, DATE)

和

DATE(SETTLEMENTDATE.VALUE)

但我需要 DB2 神童的专业知识！

But I need the expert knowledge of a DB2 whizzkid!

谢谢

推荐答案

根据您自己的回答，我猜您的列的数据格式如下:

Based on your own answer, I'm guessing that your column has data formatted like this:

'DD/MM/YYYY HH:MI:SS'

日/月/年之间的实际分隔符无关紧要，年份之后的任何分隔符也无关紧要.

The actual separators between Day/Month/Year don't matter, nor does anything that comes after the year.

您没有说明您使用的是什么版本的 DB2 或它在什么平台上运行，所以我将假设它在 Linux、UNIX 或 Windows 上运行.

You don't say what version of DB2 you are using or what platform it's running on, so I'm going to assume that it's on Linux, UNIX or Windows.

几乎所有最新版本的 DB2 for Linux/UNIX/Windows(8.2 或更高版本，甚至可能是更旧的版本)，您都可以使用 TRANSLATE 函数执行此操作:

Almost any recent version of DB2 for Linux/UNIX/Windows (8.2 or later, possibly even older versions), you can do this using the TRANSLATE function:

select 
   date(translate('GHIJ-DE-AB',column_with_date,'ABCDEFGHIJ'))
from
   yourtable

使用此解决方案，列中日期之后的内容无关紧要.

With this solution it doesn't matter what comes after the date in your column.

在 DB2 9.7 中，您还可以使用 TO_DATE 函数(类似于 Oracle 的 TO_DATE):

In DB2 9.7, you can also use the TO_DATE function (similar to Oracle's TO_DATE):

date(to_date(column_with_date,'DD-MM-YYYY HH:MI:SS'))

这要求您的数据与格式字符串匹配；它在查看时更容易理解，但不如 TRANSLATE 选项灵活.

This requires your data match the formatting string; it's easier to understand when looking at it, but not as flexible as the TRANSLATE option.

这篇关于在 DB2 中将字符串转换为日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！