CODEWITHSUNDEEP
datessis
user25174201
user25174201

Reputation: 1

SSIS OLE DB Source Editor : Where Clause for Dates

I’ve been trying to setup a SSIS Package to pull data from DB2 to SQL Server.

The format of the dates in the Orders Table is a Numeric (8,0) so today is stored as 20240521. I’m trying to limit the records to only return for the last 12 months.

All the typical methods I’d use in SSMS for date manipulations have not worked in the OLE DB Source Editor / SQL Command Text and resulted in an error. Some of the variations I’ve tried in the Where clause are:

  1. Select * from Orders Where OrdDate >= Convert(Char(8), Dateadd(y, -1, getdate()), 112)
  2. Select * from Orders Where Convert(Date, Convert(char(8), OrdDate)) >= Dateadd(year, -1, getdate())

I'm at my wits end and would really appreciate any help.

Thanks!

Upvotes: 0

Views: 32

Answers (1)

Gustav
Gustav

Reputation: 55841

You can use modulus and DateFromParts in an expression:

DateFromParts([NumDate] / 10000, [NumDate] % 10000 / 100, [NumDate] % 100) AS TrueDate

Upvotes: 0

Related Questions