Posted: Fri Oct 14, 2016 10:40 pm Post subject: Get correct date and time when current time is over 12 hrs

Hi ,
I have written a SQL query to create a date file which is used by job to fetch sales data for every past 1 hour . Problem is when current time is pass 12 hours y (i,e) by 12 AM it is fetching CURRENT DATE which is wrong , for that we are hardcoding date for the query input .

FROM TEST.CALENDER_TABLE ,
(SELECT 00 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 01 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 02 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 03 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 04 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 05 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 06 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 07 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 08 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 09 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 10 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 11 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 12 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 13 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 14 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 15 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 16 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 17 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 18 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 19 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 20 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 21 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 22 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 23 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL) AS A
WHERE GREGORIAN_DATE = CURRENT_DATE <- hardcoded everytime with current date on each day .
ORDER BY GREGORIAN_DATE,COL2
WITH UR;

Output file looks like
10/14/2016|2016.10.14|00|CLSDHOUR|09|10|
....
....,
WHEN IT RUNS at 12:30 in Night it represents as

10/15/2016|2016.10.15|23|CURRHOUR|22|23|

but expected is

10/14/2016|2016.10.14|23|CURRHOUR|22|23|

I should check for time by adding another case while populating when time crosses 12 hrs. Can any one help me in checking this case and also i need to feed the current date as input .

select
case when (current time > '23:59:59' AND
current time <= '00:59:59') then
VARCHAR_FORMAT(current date - 1 day,'MM/DD/YYYY')
else VARCHAR_FORMAT(current date,'MM/DD/YYYY') end
from sysibm.sysdummy1

Took out -1(assuming"fetch sales data for every past 1 hour" done before) but I guess range is still needed, after 24:00:00 even though we are entering into next day and that's technically true but here TS wants to go back 1 hour which then gets us back to previous day date but TS unable to get that date instead getting the next date. So basically TS wants previous date only when it is between midnight till 1am.

FROM TEST.CALENDER_TABLE ,
(SELECT 00 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 01 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 02 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 03 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 04 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 05 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 06 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 07 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 08 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 09 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 10 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 11 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 12 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 13 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 14 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 15 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 16 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 17 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 18 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 19 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 20 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 21 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 22 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 23 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL) AS A
WHERE GREGORIAN_DATE = CURRENT_DATE
ORDER BY GREGORIAN_DATE,COL2
WITH UR;

Hi Every one ,
I am able to see the output in my file . However I am seeing the low values in my output file at column 23rd position(i,e) after date format(YYYY.MM.DD) .Not sure what is the issue . Can any one help me on this?

FROM TEST.CALENDER_TABLE ,
(SELECT 00 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 01 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 02 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 03 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 04 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 05 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 06 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 07 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 08 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 09 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 10 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 11 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 12 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 13 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 14 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 15 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 16 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 17 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 18 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 19 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 20 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 21 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 22 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 23 AS COL2 FROM SYSIBM.SYSDUMMY1 UNION ALL) AS A
WHERE GREGORIAN_DATE = CURRENT_DATE
ORDER BY GREGORIAN_DATE,COL2
WITH UR;