Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need assistance in queryof current date

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Need assistance in queryof current date
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: 1013
Location: India

PostPosted: Fri Mar 13, 2009 6:00 pm    Post subject:
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    Post subject:
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    Post subject: thank you so much but still need help
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: 1013
Location: India

PostPosted: Mon Mar 16, 2009 6:15 pm    Post subject:
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    Post subject: thank you:)
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: 2238
Location: @my desk

PostPosted: Thu Mar 19, 2009 3:51 pm    Post subject:
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: 1013
Location: India

PostPosted: Thu Mar 19, 2009 4:11 pm    Post subject:
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: 2238
Location: @my desk

PostPosted: Thu Mar 19, 2009 7:13 pm    Post subject:
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: 1013
Location: India

PostPosted: Thu Mar 19, 2009 7:22 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm
No new posts How to get current date -1 and curren... murali.andaluri DFSORT/ICETOOL 3 Wed Mar 08, 2017 2:09 pm
No new posts Overlay the system date Danielle.Filteau SYNCSORT 4 Fri Mar 03, 2017 1:16 am
No new posts Convert current to hex value gopurs DFSORT/ICETOOL 4 Wed Feb 15, 2017 5:40 am
No new posts Checking Current date vickey_dw DFSORT/ICETOOL 1 Wed Feb 08, 2017 11:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us