Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2012
    Posts
    31

    Unanswered: Extract Month as 2 digits

    Hi
    I am trying to convert MS Access sql to IBM DB2 sql.
    I have a date field where the data looks like this:

    8/16/2001 12:00:00 AM

    I want to extract the month from this date field as 2 digits, so '08' in the above example.

    Month(TMD.D_SERV_END)As "Month", only gives me '8'

    how do I need to tweak this?

    Thanks
    Laura

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Not sure what kind of column that is (timestamp, date, etc) but try this:

    db2 -x "values substr(TMD.D_SERV_END,6,2)"
    07
    Last edited by Marcus_A; 07-28-12 at 09:22.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by laurastreng View Post

    Month(TMD.D_SERV_END)As "Month", only gives me '8'
    You can try
    Code:
    digits(cast(month(TMD.D_SERV_END) as decimal(2)))
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by laurastreng View Post
    Hi
    I am trying to convert MS Access sql to IBM DB2 sql.
    I have a date field where the data looks like this:

    8/16/2001 12:00:00 AM

    I want to extract the month from this date field as 2 digits, so '08' in the above example.

    Month(TMD.D_SERV_END)As "Month", only gives me '8'

    how do I need to tweak this?

    Thanks
    Laura
    Code:
    $ db2 "select CASE WHEN Month(TMD.D_SERV_END) < 10 THEN '0' else '' END || CAST(Month(TMD.D_SERV_END) AS CHAR(1)) from lateral(values current_timestamp) as TMD (D_SERV_END)"
    
    1 
    --
    07
    --
    Lennart

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by lelle12 View Post
    Code:
    $ db2 "select CASE WHEN Month(TMD.D_SERV_END) < 10 THEN '0' else '' END || CAST(Month(TMD.D_SERV_END) AS CHAR(1)) from lateral(values current_timestamp) as TMD (D_SERV_END)"
    
    1 
    --
    07
    Why would anyone write such a convoluted SQL?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Nov 2011
    Posts
    334
    why not use lpad function?

    values(lpad(month(current timestamp),2,'0'));

  7. #7
    Join Date
    Jul 2012
    Posts
    31
    Thank you for the replies.
    This code worked beautifully!

    digits(cast(month(TMD.D_SERV_END) as decimal(2)))

    Laura

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •