Excel 2007 Microsoft Query tool date range 1

[cjbrown815]

Hello, I have the following query with a fixed date range. I want to change the date range so it returns all records from todays date back three years each time the query is ran. thanks

WHERE SAMPLE.SAMPLE_ID = SAMPLEPARAM.SAMPLE_ID AND
SAMPLEPARAM.ST_N = SAMPLETYPE.ST_N AND
SAMPLEPARAM.ST_VERS = SAMPLETYPE.ST_VERS AND
((SAMPLE.SAMPLE_TYPE Like 'WKLY_%') AND
(SAMPLE.DATE3>={ts '2008-01-04 00:00:00'} And
SAMPLE.DATE3<={ts '2010-08-25 00:00:00'}) AND
(SAMPLEPARAM.PRINT_FLAG=1))
ORDER BY SAMPLE.DATE3, SAMPLE.SAMPLE_TYPE



-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[randy700]

How about...

SAMPLE.DATE3 >= TODAY() - (3 * 365)

Randy

 

[SkipVought]

hi,

Select *

From
  SAMPLE SA
, SAMPLEPARAM SP
, SAMPLETYPE ST

WHERE SA.SAMPLE_ID   = SP.SAMPLE_ID
  AND SP.ST_N        = ST.ST_N
  AND SP.ST_VERS     = ST.ST_VERS
  AND SA.SAMPLE_TYPE Like 'WKLY_%'[b]
  AND SA.DATE3       >=DATE() - 365*3
  And SA.DATE3       <=DATE()[/b]
  AND SP.PRINT_FLAG  =1

ORDER BY
  SA.DATE3
, SA.SAMPLE_TYPE

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cjbrown815]

thanks, I'm using the query tool in excel and its telling me TODAY is not a recognized built in funtion

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

use DATE() not TODAY()

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cjbrown815]

Thanks Skip, Here is the full select statement. Not sure how to incorporate the statement you just sent me

SELECT SAMPLE.SAMPLE_ID, SAMPLE.SAMPLE_STATUS, SAMPLE.SUBMITTER, SAMPLE.SAMPLE_TYPE,SAMPLE.TEXT3, SAMPLE.TEXT5,SAMPLE.DATE3,
SAMPLE.REMARKS, SAMPLE.LOT, SAMPLE.PRODUCT_LOT,
SAMPLETYPE.ST_TYP, SAMPLETYPE.ST_ID, SAMPLETYPE.ST_NAME,
SAMPLETYPE.ST_DESC,
SAMPLEPARAM.PA_NAME,
SAMPLEPARAM.TRESULT, SAMPLEPARAM.NRESULT, SAMPLEPARAM.SRESULT, SAMPLEPARAM.ORESULT,
SAMPLEPARAM.SAMPLEPARAM_ID
FROM LIMSProd.dbo.SAMPLE SAMPLE, LIMSProd.dbo.SAMPLEPARAM SAMPLEPARAM, LIMSProd.dbo.SAMPLETYPE SAMPLETYPE

WHERE SAMPLE.SAMPLE_ID = SAMPLEPARAM.SAMPLE_ID AND
SAMPLEPARAM.ST_N = SAMPLETYPE.ST_N AND
SAMPLEPARAM.ST_VERS = SAMPLETYPE.ST_VERS AND
((SAMPLE.SAMPLE_TYPE Like 'WKLY_%') AND
SAMPLE.DATE3 >= TODAY() - (3 * 365) AND
(SAMPLEPARAM.PRINT_FLAG=1))
ORDER BY SAMPLE.DATE3, SAMPLE.SAMPLE_TYPE

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

Did you not read my post of 3 May 11 12:03 ?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cjbrown815]

I did, tis giving me the same error. Date is not a recognized built in function.

SELECT SAMPLE.SAMPLE_ID, SAMPLE.SAMPLE_STATUS, SAMPLE.SUBMITTER, SAMPLE.SAMPLE_TYPE,SAMPLE.TEXT3, SAMPLE.TEXT5,SAMPLE.DATE3,
SAMPLE.REMARKS, SAMPLE.LOT, SAMPLE.PRODUCT_LOT,
SAMPLETYPE.ST_TYP, SAMPLETYPE.ST_ID, SAMPLETYPE.ST_NAME,
SAMPLETYPE.ST_DESC,
SAMPLEPARAM.PA_NAME,
SAMPLEPARAM.TRESULT, SAMPLEPARAM.NRESULT, SAMPLEPARAM.SRESULT, SAMPLEPARAM.ORESULT,
SAMPLEPARAM.SAMPLEPARAM_ID
FROM LIMSProd.dbo.SAMPLE SAMPLE, LIMSProd.dbo.SAMPLEPARAM SAMPLEPARAM, LIMSProd.dbo.SAMPLETYPE SAMPLETYPE

WHERE SAMPLE.SAMPLE_ID = SAMPLEPARAM.SAMPLE_ID AND
SAMPLEPARAM.ST_N = SAMPLETYPE.ST_N AND
SAMPLEPARAM.ST_VERS = SAMPLETYPE.ST_VERS AND
((SAMPLE.SAMPLE_TYPE Like 'WKLY_%') AND
(SAMPLE.DATE3>=DATE() - 365*3 And
SAMPLE.DATE3<=DATE() AND
(SAMPLEPARAM.PRINT_FLAG=1))
ORDER BY SAMPLE.DATE3, SAMPLE.SAMPLE_TYPE

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

What kind of DB are you querying?

You must use the SYNTAX that THAT DB uses.

For instance:

Access uses DATE()

Oracle uses SYSDATE

DB2 uses SYSDATE

???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cjbrown815]

SQL 2010

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

You're going to have to get some SQL Server help at forum183, but take a look at the GetDate() function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

and take a look at faq183-6628


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}