本文介绍了DB2将数字转换为日期的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

由于某种原因(我对此无能为力),日期以整数形式存储在我需要查询的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将数字转换为日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-01 15:02
查看更多