using calculated date in SQL

[GJParker]

I'm new to DTS and not sure of the best way to tackle this one.

I am attempting to import data into SQL2000 from an archaeic flat file database on UNIX.

The import will run each night and needs to pick out unallocated records and records allocated in the last 14 days.

I created a DTS package based on an SQL atatement

SELECT ACCNT_CODE AS AccountCode, D_C AS DebitOrCredit, TRANS_DATE, ALLOC_DATE, DUE_DATE, AMOUNT, TREFERENCE, ALLOCATION
FROM SALFLDG
WHERE (TRANS_DATE <= { fn CURDATE() }) AND (TRANS_DATE >= { d '2003-01-01' }) AND (ALLOC_DATE IS NULL OR ALLOC_DATE >= 'date 14 days ago'

My problem is that the source database doesn't have the functionality to calculate the date 14 days.

How do I calculate the date using SQL server and pass this to my SQL statement.

I know I could use VBScript to create my connections and generate the SQL, but are there any simpler ways available to me.

Thanks in advance

Gary Parker
MIS Data Analyst
Manchester, England

 

[JayKusch]

SELECT ACCNT_CODE AS AccountCode, 
       D_C        AS DebitOrCredit, 
       TRANS_DATE, 
       ALLOC_DATE, 
       DUE_DATE, 
       AMOUNT, 
       TREFERENCE, 
       ALLOCATION
FROM SALFLDG
WHERE CONVERT(Char,TRANS_DATE,110) <= CONVERT(Char,GetDate(),110)
AND CONVERT(Char,TRANS_DATE,110) >= '2003-01-01' 
AND (CONVERT(Char,ALLOC_DATE,110) >= CONVERT(Char,DateAdd(dd,-14,GetDate()),110)
OR ALLOC_DATE IS NULL)

Thanks

J. Kusch

 

[GJParker]

It's not as easy as this as the ODBC driver tries to pass the SQL statement directly to the Source database.

in the form

SELECT     ACCNT_CODE AS AccountCode, D_C AS DebitOrCredit, TRANS_DATE, ALLOC_DATE, DUE_DATE, AMOUNT, TREFERENCE, ALLOCATION
FROM         SALFLDG
WHERE     ({ fn CONVERT(Char, TRANS_DATE, 110) } <= { fn CONVERT(Char, GetDate(), 110) }) AND ({ fn CONVERT(Char, TRANS_DATE, 110) } >= '2003-01-01') AND 
                      ({ fn CONVERT(Char, ALLOC_DATE, 110) } >= { fn CONVERT(Char, DateAdd(dd, - 14, GetDate()), 110) }) OR
                      (ALLOC_DATE IS NULL) AND ({ fn CONVERT(Char, TRANS_DATE, 110) } <= { fn CONVERT(Char, GetDate(), 110) }) AND ({ fn CONVERT(Char, 
                      TRANS_DATE, 110) } >= '2003-01-01')

CONVERT and GetDate functions aren't supported functions on this database.

Gary Parker
MIS Data Analyst
Manchester, England

 

[JayKusch]

Then I am at a loss. Sorry. It seems to me that you will need to figure out the UNIX syntax to pull the records. If the flat file isn't too large, you may be able to import it into a work table in SQL Server and then run the code I listed above against it.

Thanks

J. Kusch

 

[seweryng]

Hi GJParker,

I,ve got similar problem with importing data from unix Informix db.

You can do this with use of DTS global variables.
(Right click menu, package properties, second tab).

First, you can create a SQL step, using SQL server connection. This step will set the global variable.
Then, second step will execute the SQL you created before. You can point to the global variable by using "?" chracter.

Regards,
Seweryn