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

Cube Datasource is not on SQL Server

Hi,

is it possible to define a Cube on SQL Server 2005 where the data source itself is on a DB2 or Oracle Database? I would like to have the Cube to collect the data from the DB2 Database to build the Cube. The Cube itself can copy some data to the SQL Server if necessary. What I'm trying to avoid is to have an ETL Process to copy over everything to the SQL Server and then build a Cube on top of it. It is currently very difficult to synchronize the data from the DB2 to the SQL Server. It is also not possible to go over a time period as it can happen that you get new records for the last year. We have to work with a lot of data (several million per day).

My goal is to find an intelligent Solution to make sure that the Cube has all data.

Thanks.

Ertan

Analysis Services supports building cubes by directly connecting to several relational databases. Oracle , DB2 are on the list of supported sources. Please refer to the books online to exact list of supported DB versions.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.