DATE FORMAT :DB2 _ SQL-CodePudding

I have date format (TIMESTAMP) like 2018-03-26-08.30.00.000000 and i want to get it as 2018-03-26-08 how i can do it in sql in DB2(just i want year-month-day-hour )'

CodePudding user response：

VARCHAR_FORMAT(<timestamp>, '<desired format>')

So:

SELECT VARCHAR_FORMAT(CURRENT TIMESTAMP, 'YYYY-MM-DD-hh24')
FROM SYSIBM.SYSDUMMY1

CodePudding user response：

Read DB2's manual: https://www.ibm.com/docs/en/db2-for-zos/12?topic=sf-char

Using that docu:

I need to convert the non-standard in-format with a dash between day and hour, and dots as hour/minute and minute/second separators to a timestamp, using TO_TIMESTAMP().
From the obtained timestamp, I can use TO_CHAR() with a format string to give me back a string with the format I desire.

WITH
indata(ts) AS (
  SELECT 
    TO_TIMESTAMP(
     '2018-03-26-08.30.00.000000','YYYY-MM-DD-HH24.MI.SS.US'
    ) 
  FROM sysibm.sysdummy1
)
SELECT TO_CHAR(ts,'YYYY-MM-DD-HH24') AS new_format FROM indata;
  new_format    
---------------
 2018-03-26-08