Extracting month, day, year from dates

I need help in building a Select statement for an ODBC connection to an AS400 DB2 system. I am using this Select statement for SQL Reporting Services.

Unfortunately 15 years ago one of the main datatables on the AS400 was set up splitting the transaction date into THREE FIELDS! One for Month, one for Day and then one for Year.

I need to take yesterday's date and somehow use this in my WHERE statement to connect the transaction records that are in this AS400 table. This is what I have now that is not working:

WHERE QS36F.BRDMST."BREED#" = QS36F.DOGOWNR.BRDCOD AND (QS36F.DOGOWNR.TMONTH = "MONTH"(GETDATE() ) AND
(QS36F.DOGOWNR.TDAY = "DAY"(GETDATE()-1 ) AND (QS36F.DOGOWNR.TYEAR = "YEAR"(GETDATE())
GROUP BY QS36F.BRDMST.BRDESC

How do I extract each day, month, and year from the current date?

Thanks for the information

SqlServer has a DatePart function. Is that what you're looking for?

Mike

|||

Mike,

I did try the DATEPART function and was getting an AS400 ODBC Client Express error. I do believe I solved the problem, however I just had this query running over two hours and just killed it. Here is the SELECT statement:

SELECT COUNT(QS36F.BRDMST.BRDESC) AS BreedCount, QS36F.BRDMST.BRDESC
FROM QS36F.BRDMST, QS36F.DOGOWNR
WHERE QS36F.BRDMST."BREED#" = QS36F.DOGOWNR.BRDCOD AND (QS36F.DOGOWNR.TMONTH = "MONTH"(NOW())) AND
(QS36F.DOGOWNR.TDAY = "DAY"(NOW()) - 1) AND (QS36F.DOGOWNR.TYEAR = "YEAR"(NOW()))
GROUP BY QS36F.BRDMST.BRDESC

The DOGOWNR table has about 1,500,000 records.

I need this to be a lot quicker. Might you have any suggestions? My thought is to use SSIS to extract all of the data into SQL from the AS400 and then build the reports through that, except that SSIS will have to run nightly to copy over any differential data.

Thanks for the information.

|||

Wish I could help, but I'm no DBA. I wonder where the bottlenecks are, guessing it's the ODBC business and not the query? Would be interesting to run that query right on the AS400 and see how fast it is.

I did a quick google trying to see if there's a .NET driver available. I found this - http://cc.borland.com/Item.aspx?id=23616 - not sure if it will help or not.

Mike

|||

Mike,

Our AS400 contractor happens to be here today and I asked her. She said that for the AS400 itself to query this datatable it takes about 1/2 an hour. I do believe too it has something to do with ODBC which is why I might go the SSIS route and replicate the data that is needed.

I will also check out that .Net driver. Thanks for the help.

|||You are going to have a problem with that if it is the 1st of the month or of the year. Make sure you are using Now() or Now() -1 in all instances, not a combination of the two.

If your query is running much longer than expected you might want to make sure the data types match up. If your TYear field and Year(Now()) are different data types it is possible that your TYear is being converted rather than the result of Year(Now()). If this was the case I don't think your index would be used. In any case check your query plan to be sure.

When you check the plan, make sure that the expected table is driving the query. I am not familiar with AS400 but in some database implementation this is critical.

count(*) is often more effecient than count(column). Since you are grouping on the column being counted you don't really need count to filter out counting of the null columns.

You might also experiment with a view that does the counting for you, then join the view back tot he breed table.

Create View OwerCountByBreed As
Select brdcod, count(*) "TheCount"
From dogownr
Where
TYear = Year(Now()-1) And
TMonth = Month(Now()-1) And
TDay = Day(Now-1)
Group By brdcod

Select brddesc, TheCount
From mrdmst, ownercountbybreed
Where brdmst.breed# = ownercountbybreed.brdcod

Just some thoughts. Sorry if this was all obvious.
|||

Some of that was obvious but not thought of by me so thank you for the reminders (like having be the first day of the month). I think maybe an easier solution may be to do a DATEADD. I don't know, I will figure it out today.

I also am going to try using the IBM AS400 OleDB adapter as opposed to using ODBC and see if that helps any.

I am mainly a developer, not a DB Admin. I am very familiar with simple SELECT statements. I am learning more and more about advanced SELECT statements.

|||

Hi,

I have some experience in AS400/DB2 - it look to me like you are running this on system 36 flat files (system 36 environment on the AS400) am I correct?

This may be a mute point - but have you checked the indexing on the DB2 database? Adding one index may well speed things up.

Also, what anti-virus do you have on your PC? Strange question? I have know Norton realtime scan to try and check all data as it was returned via ODBC - just a thought.

|||I was able to use SSIS useing Derived Colums to pull the data into a Flat File. I then copied the flat file into SQL. The initial pull from the AS400 took about two hours for almost 2.5 million records, but the flat file to SQL took about 30 seconds.