CODEWITHSUNDEEP
sqldb2
Tick _Tack
Tick _Tack

Reputation: 33

DATE FORMAT :DB2 _ SQL

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 )'

Upvotes: 0

Views: 4542

Answers (2)

marcothesane
marcothesane

Reputation: 6721

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

Using that docu:

  1. 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().
  2. 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

Upvotes: 1

Andrew
Andrew

Reputation: 8703

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

So:

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

Upvotes: 0

Related Questions