IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Need assistance in queryof current date


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
nandy.paromita

New User


Joined: 04 Dec 2007
Posts: 4
Location: Chennai

PostPosted: Fri Mar 13, 2009 4:00 pm
Reply with quote

Hi all,

I have a query that extracts the month (current month -1) . I need it in 'MM' format and it retrieves as 'M'.
Eg: Current month -1 = 03-01 = 02 but the query retrieves only 2.
SELECT
CHAR(MONTH(CURRENT_DATE - DAY(CURRENT_DATE)DAYS))
FROM SYSIBM.SYSDUMMY1
WITH UR;

PLease help !

Thanks,
Nandy icon_smile.gif
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Fri Mar 13, 2009 6:00 pm
Reply with quote

Welcome Nandy,

Check it out,
Code:
SELECT                                             
 DIGITS(MONTH(CURRENT_DATE - DAY(CURRENT_DATE)DAYS))
 FROM SYSIBM.SYSDUMMY1                               
 WITH UR;


For your exact answer use SUBSTR.

Thank You,
Sushanth Bobby
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Mar 13, 2009 6:02 pm
Reply with quote

Code:


05 LAST-MONTH            PIC S9(9) COMP-5.

SELECT
MONTH(CURRENT_DATE - DAY(CURRENT_DATE)DAYS)
INTO :LAST-MONTH
FROM SYSIBM.SYSDUMMY1
WITH UR;


The CHAR function supresses leading zeros.
Back to top
View user's profile Send private message
nandy.paromita

New User


Joined: 04 Dec 2007
Posts: 4
Location: Chennai

PostPosted: Mon Mar 16, 2009 5:59 pm
Reply with quote

Hi Sushanth and Craq,

Thank you so much for the response but still desired results are not obtained.
My complete query is:
SELECT
DEC(CONCAT(SUBSTR(CHAR(YEAR(CURRENT_DATE - DAY(CURRENT_DATE)DAYS)),1,4),
DIGITS(MONTH(CURRENT_DATE - DAY(CURRENT_DATE)DAYS))))
FROM SYSIBM.SYSDUMMY1
WITH UR;

and the result is 20090000000002.

whereas i am expecting 200902.

Please suggest.

Thanks,
Nandy
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Mon Mar 16, 2009 6:15 pm
Reply with quote

Try to play with functions a little-bit Nandy,

Check this out,
Code:
SELECT CONCAT(STRIP(CHAR(YEAR(CURRENT DATE))),                 
SUBSTR(DIGITS(MONTH(CURRENT_DATE - DAY(CURRENT_DATE)DAYS)),9,2))
FROM SYSIBM.SYSDUMMY1;                                         


Sushanth
Back to top
View user's profile Send private message
nandy.paromita

New User


Joined: 04 Dec 2007
Posts: 4
Location: Chennai

PostPosted: Thu Mar 19, 2009 1:44 pm
Reply with quote

Thank you so much Sushanth, the query is perfect !
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Mar 19, 2009 3:51 pm
Reply with quote

Here's another method which I think would give the same results.
Code:
SELECT SUBSTR(CHAR(CURRENT DATE - 1 MONTH),7,4) ||
       SUBSTR(CHAR(CURRENT DATE - 1 MONTH),1,2)   
FROM SYSIBM.SYSDUMMY1;                           
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Thu Mar 19, 2009 4:11 pm
Reply with quote

Arun,

Can you re-check your query ?
Cause iam getting a different answer(2-1920) than what NANDY is expecting(200902).

Thank You,
Sushanth
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Mar 19, 2009 7:13 pm
Reply with quote

Sushanth,

The SQL which I provided is works fine for me and the solution is installation dependent. It depends on the format in which CURRENT_DATE is presented by DB2 on your system. My shop uses the IBM standard for U.S. date and time format (USA) ie. MM/DD/YYYY.

You can still convert the default format to any desired format using the CHAR function. Say for e.g. to convert it to ISO format (YYYY-MM-DD) you can simply specify CHAR(CURRENT DATE,ISO). By overriding the default format you can make this work on any system.
Code:
SELECT SUBSTR(CHAR(CURRENT DATE - 1 MONTH,USA),7,4) ||
       SUBSTR(CHAR(CURRENT DATE - 1 MONTH,USA),1,2)   
FROM SYSIBM.SYSDUMMY1
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Thu Mar 19, 2009 7:22 pm
Reply with quote

Arun,

I did'nt know that.
I think your query is more efficient than mine. I have used lots of function.

Thank You,
Sushanth Bobby
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts To get the the current time DFSORT/ICETOOL 13
No new posts Need to convert date format DFSORT/ICETOOL 20
No new posts Changeman - how can we know the curr... Compuware & Other Tools 2
No new posts Need help to append a date&tsp at... DFSORT/ICETOOL 9
Search our Forums:

Back to Top