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.

ThanksDECLARE @.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|||Thanks a lot. That helped.
Originally posted by Brett Kaiser

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,

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

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|||My Bad:

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|||Thanks Brett. Happy New Year to you too.
Originally posted by Brett Kaiser
My Bad:

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