Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: Current Date help

    Hello DBA's:

    I have a linked server to DB2. The date field in my DB2 source files is of the format 1031222 (7 character). The 1 indicates 21th century.Year 2000 and beyond. 03 is year 2003,12 is the month, 22 is the day. Now this date is the current date. Tommorow would be 1031223.

    Records are added to this file on a daily basis with the current date. Now I need to query this file on a daily basis using my linked server. An example query would be.

    Select *
    From DB2LinkedServer.Filename
    where Date = CurrentDate.

    Now How do I implement this current date. I am not sure how do I include the function getdate() and get to the CurrentDate format of my source data.

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    DECLARE @bogusDate char(7)
    SELECT @bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate())),1,1) = 2 THEN '1' ELSE '0' END
    	+ RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)
    	+ CONVERT(char(2),DATEPART(mm,GetDate()))
    	+ CONVERT(char(2),DATEPART(dd,GetDate()))
    SELECT @bogusDate
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Posts
    176
    Thanks a lot. That helped.
    Originally posted by Brett Kaiser
    Code:
    DECLARE @bogusDate char(7)
    SELECT @bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate())),1,1) = 2 THEN '1' ELSE '0' END
    	+ RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)
    	+ CONVERT(char(2),DATEPART(mm,GetDate()))
    	+ CONVERT(char(2),DATEPART(dd,GetDate()))
    SELECT @bogusDate

  4. #4
    Join Date
    Sep 2003
    Posts
    176

    Response to Brett's code

    Brett,

    You had sent the me this piece of code pertaining to the current date help topic.

    Bretts Code:

    DECLARE @bogusDate char(7)
    SELECT @bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate())
    ),1,1) = 2 THEN '1' ELSE '0' END
    + RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)

    + CONVERT(char(2),DATEPART(mm,GetDate()))
    + CONVERT(char(2),DATEPART(dd,GetDate()))
    SELECT @bogusDate

    When you execute the above code for month < 10 or/and day < 10 you get - 1041 7 for Jan 7,2004. I want 1040107.

    So I changed the above code to -

    DECLARE @bogusDate char(7)
    SELECT @bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate())
    ),1,1) = 2 THEN '1' ELSE '0' END
    + RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)

    + CASE WHEN SUBSTRING(CONVERT(char(2),DATEPART(mm,GetDate())), 1,2) < 10
    THEN '0'+CONVERT(char(2),DATEPART(mm,GetDate()))
    ELSE CONVERT(char(2),DATEPART(mm,GetDate()))
    END

    + CASE WHEN SUBSTRING(CONVERT(char(2),DATEPART(dd,GetDate())), 1,2) < '10'
    THEN '0'+CONVERT(char(2),DATEPART(dd,GetDate()))
    ELSE CONVERT(char(2),DATEPART(dd,GetDate()))
    END
    SELECT @bogusDate

    ----

    However the day part doesn't change. I have been able to modify the month. Can you help me fix the code.

    Thanks for your help.

    Vivek



    Originally posted by Brett Kaiser
    Code:
    DECLARE @bogusDate char(7)
    SELECT @bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate())),1,1) = 2 THEN '1' ELSE '0' END
    	+ RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)
    	+ CONVERT(char(2),DATEPART(mm,GetDate()))
    	+ CONVERT(char(2),DATEPART(dd,GetDate()))
    SELECT @bogusDate

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    My Bad:

    Code:
    DECLARE @bogusDate char(7)
    SELECT @bogusDate = CASE WHEN  SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate())
    ),1,1) = 2 THEN '1' ELSE '0' END
    	+  RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)
    	+ RIGHT(REPLICATE('0',2)+CONVERT(varchar(2),DATEPART(mm,GetDate())),2)
    	+ RIGHT(REPLICATE('0',2)+CONVERT(varchar(2),DATEPART(dd,GetDate())),2)
    SELECT @bogusDate
    HAPPY NEW YEAR
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Sep 2003
    Posts
    176
    Thanks Brett. Happy New Year to you too.
    Originally posted by Brett Kaiser
    My Bad:

    Code:
    DECLARE @bogusDate char(7)
    SELECT @bogusDate = CASE WHEN  SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate())
    ),1,1) = 2 THEN '1' ELSE '0' END
    	+  RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)
    	+ RIGHT(REPLICATE('0',2)+CONVERT(varchar(2),DATEPART(mm,GetDate())),2)
    	+ RIGHT(REPLICATE('0',2)+CONVERT(varchar(2),DATEPART(dd,GetDate())),2)
    SELECT @bogusDate
    HAPPY NEW YEAR

Posting Permissions

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