DB2 将数字转换为日期 [英] DB2 Convert Number to Date

问题描述

由于某种原因(我无法控制)，日期以整数形式存储在 iSeries AS400 DB2 系统中，我需要对其进行查询.例如.今天将被存储为:

For some reason (I have no control over this) dates are stored as Integers in an iSeries AS400 DB2 system that I need to query. E.g. today will be stored as:

20,171,221

在英国，我需要它的日期格式如下所示:

Being in the UK I need it to be like the below in Date format:

21/12/2017

这是来自我的查询:(OAORDT = 日期字段)

This is from my query: (OAORDT = date field)

Select
Date(SUBSTR( CHAR( OAORDT ),7,2) ||'/' || SUBSTR(CHAR ( OAORDT ),5,2) || '/' || SUBSTR(CHAR (OAORDT ),1,4)) AS "Order Date"
from some.table

但是，我得到的只是空值.如果我删除 Date 函数，那么它确实可以工作，但它现在是一个字符串，我不想要它:

However, all I get is Nulls. If I remove the Date function, then it does work but its now a string, which I don't want:

Select
SUBSTR( CHAR( OAORDT ),7,2) ||'/' || SUBSTR(CHAR ( OAORDT ),5,2) || '/' || SUBSTR(CHAR (OAORDT ),1,4) AS "Order Date"
from some.table

如何将 OAORDT 字段转换为日期?

How do I convert the OAORDT field to Date?

只是为了更新 - 我将使用 OpenQuery 从 MS SQL Server 查询它

Just to update - I will be querying this from MS SQL Server using an OpenQuery

谢谢.

推荐答案

1) 如何将 OAORDT 字段转换为日期?
最简单的是使用 TIMESTAMP_FORMAT :

1) How do I convert the OAORDT field to Date?
Simplest is to use TIMESTAMP_FORMAT :

SELECT DATE(TIMESTAMP_FORMAT(CHAR(OAORDT),'YYYYMMDD'))

2) 在英国，我需要 [...] 日期格式 21/12/2017 :

2) Being in the UK I need it to be [...] in Date format 21/12/2017 :

SELECT VARCHAR_FORMAT(DATE(TIMESTAMP_FORMAT(CHAR(OAORDT),'YYYYMMDD')),'DD/MM/YYYY')

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