将YYYYMMDD转换为Excel dd/mm/yy [英] Convert YYYYMMDD to Excel dd/mm/yy

问题描述

在这篇文章中Gert Grenander建议格式化日期字段到"yyyy-mm-dd hh:mm:ss".

In this post Gert Grenander makes a suggestion to format the date field to 'yyyy-mm-dd hh:mm:ss'.

如何在SQL调用中使用相同的方法将"YYYYMMDD"转换为"dd/mm/yy"?

How would I convert from 'YYYYMMDD' to 'dd/mm/yy' in my SQL call using the same method?

推荐答案

select date2, 
       digits(date2), 
       (substr(digits(date2),7,2) concat '/' concat 
        substr(digits(date2),5,2) concat '/' concat 
        substr(digits(date2),3,2) 
       ) as mmddyy
  from datesample

给予:

  Signed                       CHAR       
 data type  DIGITS ( DATE2 )  MMDDYY  
----------  ----------------  --------
20130711    20130711          11/07/13

您需要通过DIGITS将十进制值(DATE2)转换为字符串，然后使用SUBSTR提取所需的段，然后使用CONCAT(或||)重新组装它们，包括所需的定界符.如果您的日期"列是字符，则可以省去转换为字符的操作.

You'll need to convert the decimal value (DATE2) to string via DIGITS, then use SUBSTR to extract the pieces you need, then use CONCAT (or ||) to reassemble them including the delimiter you want. If your 'date' column is character, you can leave out the conversion to character.

select date4, 
       (substr(date4,7,2) concat '/' concat 
        substr(date4,5,2) concat '/' concat 
        substr(date4,3,2) 
       ) as mmddyy
  from datesample

给予:

  CHAR     CHAR        
data type  MMDDYY 
---------  --------
20130711   11/07/13

这篇关于将YYYYMMDD转换为Excel dd/mm/yy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！