问题描述
由于某种原因(我对此无能为力),日期以整数形式存储在我需要查询的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 = date字段)
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
但是,我得到的只是Nulls。如果删除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字段转换为Date? >
最简单的方法是使用 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)在英国我需要日期格式为 2017/12/21
:
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将数字转换为日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!