Newbie having trouble pulling a date field from an iSeries with MS SQL

Question

Newbie having trouble pulling a date field from an iSeries with MS SQL

kabbott

SSC Journeyman

Points: 91
More actions
February 5, 2019 at 11:52 am

#370532

Caveat: I've been using SQL in any real capacity for about 3 weeks now.
My goal is we need to run a query to our iSeries AS400 and I need to use a date field to be able to say "Show me any accounts with an active date in the last 6 months." I'm struggling to get the field formatted into anything useful.
Here is the query I ran to view the date field. DLA6 is the one we really need to work with, but DLA7 is also the same date, just in Julian format.
Is there a way to query straight to the iseries to return accounts where the DLA6 (Date last active) is < 6 months ago from the current date? Or do I need to import the query results into a table and convert it somehow. I've not had success either way.
TL;DR - Need query to pull accounts where last active date is less than 6 months ago. Date field is Gregorian MMDDYY.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

below86 SSChampion Points: 11550 More actions · Answer 1

Try adding this WHERE statement:
WHERE DLA7 >= DATEPART(YEAR, DATEADD(MM, -6, GETDATE())) * 1000 + DATEPART(dy, DATEADD(MM, -6, GETDATE()))
The DATEADD is subtracting 6 months from the current date, then that is converted to a 'Julian' date.

Here is some I've used to convert the date to Julian and back :
DECLARE @input_date DATETIME;

SELECT @input_date = GETDATE();

SELECT DATEPART(YEAR, @input_date) * 1000 + DATEPART(dy, @input_date) AS Julian_Date, @input_date AS input_date;

DECLARE @jul_date INT;

SET @jul_date = (SELECT DATEPART(YEAR, @input_date) * 1000 + DATEPART(dy, @input_date));

SELECT @jul_date AS jul_date;

SELECT DATEADD(dd, CAST(RIGHT(@jul_date, 3) AS INT) - 1, CAST(CONCAT('01/01/', LEFT(@jul_date, 4)) AS DATETIME)) AS in_date

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

kabbott SSC Journeyman Points: 91 More actions · Answer 2

That seems to be working when I import the data into a table and query it from there. Trying to query straight from the iseries is giving me fits. There is something lost in translation between SQL and the iSeries.

I'll probably end up importing the data and running with what you just gave me. Thank you!

sgmunson SSC Guru Points: 110640 More actions · Answer 3

kabbott - Tuesday, February 5, 2019 12:41 PM
That seems to be working when I import the data into a table and query it from there. Trying to query straight from the iseries is giving me fits. There is something lost in translation between SQL and the iSeries.
I'll probably end up importing the data and running with what you just gave me. Thank you!

You would be best served by using the query that is within your openquery's query to do the date selection. I remember doing a contract once where a re-insurance company needed me to show them how to put together an SSRS setup that could report on data from an AS400. As I recall, I had to SET DATEFORMAT in the SQL ahead of the openquery in order for the linked server to translate the date values correctly, and I can't recall if it was SET DATEFORMAT DMY or SET DATEFORMAT MDY or SET DATEFORMAT YMD.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)