Convert Yyyymmdd To Excel Dd/mm/yy

In this post Gert Grenander makes a suggestion to format the date field to 'yyyy-mm-dd hh:mm:ss'. How would I convert from 'YYYYMMDD' to 'dd/mm/yy' in my SQL call using the same me

Solution 1:

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

gives:

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

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

gives:

CHARCHAR        
data type  MMDDYY 
---------  --------2013071111/07/13

Solution 2:

You can use CONVERT function in SQL for Converting to desired format

SELECTCONVERT(VARCHAR(15), @your_date, 103)

More Here