Skip to content

Instantly share code, notes, and snippets.

@forstie

forstie/Dates and TIMESTAMP_FORMAT

Last active March 4, 2022 15:57
Show Gist options
  • Star 3 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save forstie/4305532c14a4c2f032d1b2bd4df8b151 to your computer and use it in GitHub Desktop.
Save forstie/4305532c14a4c2f032d1b2bd4df8b151 to your computer and use it in GitHub Desktop.
Download ZIP
Formatting date data into true date and time date types
Raw
Dates and TIMESTAMP_FORMAT
-- Author: Scott Forstie
-- Email: forstie@us.ibm.com
create or replace variable coolstuff.decdate dec(6,0);
set coolstuff.decdate = '190718';
-- July 18, 2019 (yes, really!)
values timestamp_format(varchar(coolstuff.decdate), 'YYMMDD');
-- Wow
-- Yowza
-- Really?
-- Since when?
-- Nice!
-- How about this one...
--
-- Convert CYYMMDD to a date
--
create or replace variable coolstuff.cldate char(7);
set coolstuff.cldate = '2190719';
select
date(timestamp_format(
case
when substr(coolstuff.cldate, 1, 1) = '0'
then overlay(coolstuff.cldate, '19', 1, 1)
else overlay(coolstuff.cldate, '20', 1, 1)
end, 'YYYYMMDD')) as its_a_date
from sysibm.sysdummy1;
stop;
SELECT
TIMESTAMP_FORMAT('2015-09-04 13-00-01:765376123456',
'YYYY-MM-DD HH:MI:SS:FF12')
,TIMESTAMP_FORMAT('2015-09-04 13-00-01:765376123456',
'YYYY-MM-DD HH24:MI:SS:FF12',12)
,TIMESTAMP_FORMAT('2015-09-04 01:00:01PM',
'YYYY-MM-DD HH12:MI:SSAM')
,TIMESTAMP_FORMAT('2015-09-04 13-00-01',
'YYYY-MM-DD HH24:MI:SS')
,TIMESTAMP_FORMAT('2457270 01-00-13',
'J SS:MI:HH')
FROM SYSIBM.SYSDUMMY1;
SELECT
TIMESTAMP_FORMAT('SEPTEMBER:2015:04 01-00-13',
'MONTH:YYYY:DD SS:MI:HH')
,TIMESTAMP_FORMAT('September:2015:04 01-00-13',
'Month:YYYY:DD SS:MI:HH')
,TIMESTAMP_FORMAT('september:015:04 01-00-13',
'month:YYY:DD SS:MI:HH')
,TIMESTAMP_FORMAT('september:5:04 01-00-13',
'month:Y:DD SS:MI:HH')
FROM SYSIBM.SYSDUMMY1;
SELECT
TIMESTAMP_FORMAT(DAYOFWEEK(CURRENT DATE)
CONCAT ' 13:11:9915 59-57-11',
'D DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(DAYNAME(CURRENT DATE)
CONCAT ' 13:11:2015 59/57/11',
'Day DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(DAYOFMONTH(CURRENT DATE)
CONCAT ':13:9915 59,57,11',
'DD:MM:YYYY SS:MI:HH')
,TIMESTAMP_FORMAT(SUBSTR(DAYNAME(CURRENT DATE),1,3)
CONCAT ' 13:11:2015 59;57;11',
'Dy DD:MM:RRRR SS:MI:HH')
FROM SYSIBM.SYSDUMMY1;
SELECT
TIMESTAMP_FORMAT('NOV:15:11 10-15',
'MON:YY:DD MI:HH')
,TIMESTAMP_FORMAT('Nov:15:11 15',
'Mon:RR:DD HH')
,TIMESTAMP_FORMAT('nov:7715:11 ',
'mon:RRRR:DD ')
FROM SYSIBM.SYSDUMMY1;
SELECT
TIMESTAMP_FORMAT('NOVEMBER:2015:11 19-10-15',
'MONTH:YYYY:DD SS:MI:HH')
,TIMESTAMP_FORMAT('November:2015:11 19-10-15',
'Month:YYYY:DD SS:MI:HH')
,TIMESTAMP_FORMAT('november:015:11 19-10-15',
'month:YYY:DD SS:MI:HH')
,TIMESTAMP_FORMAT('november:5:11 19-10-15',
'month:Y:DD SS:MI:HH')
,TIMESTAMP_FORMAT('NOV:15:11 10-15',
'MON:YY:DD MI:HH')
,TIMESTAMP_FORMAT('Nov:15:11 15',
'Mon:RR:DD HH')
,TIMESTAMP_FORMAT('nov:7715:11 ',
'mon:RRRR:DD ')
FROM SYSIBM.SYSDUMMY1;
SELECT
TIMESTAMP_FORMAT(DAYOFWEEK(CURRENT DATE)
CONCAT ' 12:11:9915 19-10-15',
'D DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(DAYNAME(CURRENT DATE)
CONCAT ' 12:11:2015 19-10-15',
'Day DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(UPPER(DAYNAME(CURRENT DATE))
CONCAT ' 12:11:2015 19-10-15',
'DAY DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(lower(DAYNAME(CURRENT DATE))
CONCAT ' 12:11:2015 19-10-15',
'day DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(DAYOFMONTH(CURRENT DATE)
CONCAT ':12:2015 19-10-15',
'DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(DAYOFYEAR(CURRENT DATE)
CONCAT ':2015 19-10-15',
'DDD:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(SUBSTR(DAYNAME(CURRENT DATE),1,3)
CONCAT ' 12:11:2015 19-10-15',
'Dy DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(SUBSTR(UPPER(DAYNAME(CURRENT DATE)),1,3)
CONCAT ' 12:11:2015 19-10-15',
'DY DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(SUBSTR(lower(DAYNAME(CURRENT DATE)),1,3)
CONCAT ' 12:11:2015 19-10-15',
'dy DD:MM:RRRR SS:MI:HH')
FROM SYSIBM.SYSDUMMY1;
SELECT
TIMESTAMP_FORMAT(DAYOFWEEK(CURRENT DATE)
CONCAT ' 12:11:9915 19-10-15',
'D DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(lower(DAYNAME(CURRENT DATE))
CONCAT ' 12:11:2015 19-10-15',
'day DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(DAYOFMONTH(CURRENT DATE)
CONCAT ':12:9915 19-10-15',
'DD:MM:YYYY SS:MI:HH')
,TIMESTAMP_FORMAT(DAYOFYEAR(CURRENT DATE)
CONCAT ':2015 19-10-15',
'DDD:RRRR SS:MI:HH')
FROM SYSIBM.SYSDUMMY1;
SELECT
TIMESTAMP_FORMAT(DAYOFWEEK(CURRENT DATE)
CONCAT ' 12:11:9915 19-10-15',
'D DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(DAYNAME(CURRENT DATE)
CONCAT ' 12:11:2015 19-10-15',
'Day DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(DAYOFYEAR(CURRENT DATE)
CONCAT ':2015 19-10-15',
'DDD:RRRR SS:MI:HH')
FROM SYSIBM.SYSDUMMY1;
SELECT
TIMESTAMP_FORMAT(DAYOFWEEK(CURRENT DATE) CONCAT ' 11:11:2015 19-10-15',
'D DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(DAYNAME(CURRENT DATE) CONCAT ' 11:11:2015 19-10-15', 'Day DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(UPPER(DAYNAME(CURRENT DATE)) CONCAT ' 11:11:2015 19-10-15', 'DAY DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(lower(DAYNAME(CURRENT DATE)) CONCAT ' 11:11:2015 19-10-15', 'day DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(DAYOFMONTH(CURRENT DATE) CONCAT ':11:2015 19-10-15' , 'DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(DAYOFYEAR(CURRENT DATE) CONCAT ':2015 19-10-15' , 'DDD:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(SUBSTR(DAYNAME(CURRENT DATE),1,3) CONCAT ' 11:11:2015 19-10-15', 'Dy DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(SUBSTR(UPPER(DAYNAME(CURRENT DATE)),1,3) CONCAT ' 11:11:2015 19-10-15', 'DY DD:MM:RRRR SS:MI:HH')
,TIMESTAMP_FORMAT(SUBSTR(lower(DAYNAME(CURRENT DATE)),1,3) CONCAT ' 11:11:2015 19-10-15', 'dy DD:MM:RRRR SS:MI:HH')
FROM SYSIBM.SYSDUMMY1;
SELECT
TIMESTAMP_FORMAT('NOVEMBER:2015:11 19-10-15',
'MONTH:YYYY:DD SS:MI:HH')
,TIMESTAMP_FORMAT('November:2015:11 19-10-15',
'Month:YYYY:DD SS:MI:HH')
,TIMESTAMP_FORMAT('november:015:11 19-10-15',
'month:YYY:DD SS:MI:HH')
,TIMESTAMP_FORMAT('november:5:11 19-10-15',
'month:Y:DD SS:MI:HH')
FROM SYSIBM.SYSDUMMY1;
SELECT
TIMESTAMP_FORMAT(lower(DAYNAME(CURRENT DATE)) CONCAT ' 11:11:2015 19-10-15', 'day DD:MM:RRRR SS:MI:HH')
FROM SYSIBM.SYSDUMMY1;
values(DAYNAME(CURRENT DATE));
SELECT
TIMESTAMP_FORMAT('nov:15:11 ', 'mon:RRRR:DD ')
FROM SYSIBM.SYSDUMMY1;
SELECT
TIMESTAMP_FORMAT('2457338 19-10-15', 'J SS:MM:HH')
FROM SYSIBM.SYSDUMMY1;
SELECT
VARCHAR_FORMAT('2015-11-11 15:18:15.765376', 'YYYY-MM-DD HH:MI:SS',12)
,VARCHAR_FORMAT('2015-11-11 15:10:19.011476', 'YYYY-MM-DD HH12:MI:SS')
,VARCHAR_FORMAT('2015-11-11 15:10:19.011476', 'YYYY-MM-DD HH24:MI:SS')
FROM SYSIBM.SYSDUMMY1;
values (day (current date));
values (current_timestamp(12));
values(julian_day(DATE('2015-09-04')));
SELECT
TIMESTAMP_FORMAT('2015-11-11 15:18:15', 'YYYY-MM-DD HH12:MI:SS')
,TIMESTAMP_FORMAT('2015-11-11 15:18:15', 'YYYY-MM-DD HH24:MI:SS')
FROM SYSIBM.SYSDUMMY1;
-- Wants 2018-01-12-07.34.21.000000
SELECT
TIMESTAMP_FORMAT( '01122018073421',
'MMDDRRRRHHMISS')
FROM SYSIBM.SYSDUMMY1;
values CURRENT TIMESTAMP+12 hours;
select CURRENT TIMESTAMP+12 hours,
VARCHAR_FORMAT(CURRENT TIMESTAMP+12 hour, 'YYYYMMDDHH24MI')
from sysibm.sysdummy1;
select CURRENT TIMESTAMP+12 hours,
VARCHAR_FORMAT(CURRENT TIMESTAMP+12 hour, 'YYMMDD')
from sysibm.sysdummy1;
-- Wants 2018-01-12-07.34.21.000000
SELECT
DATE(TIMESTAMP_FORMAT( '010618',
'MMDDRR') )
FROM SYSIBM.SYSDUMMY1;
-- 12082018
SELECT
VARCHAR_FORMAT( current timestamp,
'MMDDYYYY')
FROM SYSIBM.SYSDUMMY1;
SELECT
VARCHAR_FORMAT( current date,
'MMDDYYYY')
FROM SYSIBM.SYSDUMMY1;
@robberendt
Copy link

Would be nice if easy conversion from ISO 3601 format was available.
2019-07-17T15:21:38Z
2019-07-17T15:21:38+01:00
First, there's that T...
Z = Zulu time
+01:00 is Zulu + 1 hour

@forstie
Copy link
Author

forstie commented Jul 19, 2019

I added a CYYMMDD example.

@robberendt
Copy link

I think I meant ISO 8601 format

http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=134777

@FLYERFIGHTER66
Copy link

Good stuff Scott! I was struggling with date/time data not that long ago... TIMESTAMP_FORMAT saved the day!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment