Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I query on currentdate -365*3 in SQL 2010 2

Status
Not open for further replies.
cjbrown815

cjbrown815

IS-IT--Management
Mar 2, 2006
525
US
Hello, I have a fixed date that I need to make today minus 3 years each time it is ran. Here is the current query. I am using excel 2007 Microsoft query tool to return the data in a spreadsheet for plotting
Thanks

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>={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
 
Sort by date Sort by votes
Code: 
Select GetDate(), GetDate()-(365*3), DateAdd(yy, -3, GetDate())

Lodlaiden

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]
 
Upvote 0 Downvote
Thanks, I'm getting a bad comma error. incorrect syntex near ','

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931
 
Upvote 0 Downvote
What does your code now look like?

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]
 
Upvote 0 Downvote
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>=GetDate(), GetDate()-(365*3), DateAdd(yy, -3, GetDate())

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
 
Upvote 0 Downvote
(SAMPLE.DATE3>=GetDate(), GetDate()-(365*3), DateAdd(yy, -3, GetDate())

should be

(SAMPLE.DATE3>=DateAdd(yy, -3, GetDate())

Lodlaiden

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]
 
Upvote 0 Downvote
Thanks, Incorrect syntax near the keyword 'ORDER', Statements could not be prepared. Yet if I keep the original staement, the statement works.

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931
 
Upvote 0 Downvote
...current code...

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]
 
Upvote 0 Downvote
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>=DateAdd(yy, -3, GetDate()) 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
 
Upvote 0 Downvote
Hint: You should have the same number of close parenthesis as you have open parenthesis.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Hey george, I just counted, they are correct. Thanks for the reply

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931
 
Upvote 0 Downvote
I counted too, and it appears as though you are missing a close parenthesis at the end.

[tt][blue]
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>=DateAdd(yy, -3, GetDate()) AND
(SAMPLEPARAM.PRINT_FLAG=1))[!])[/!]
ORDER BY SAMPLE.DATE3, SAMPLE.SAMPLE_TYPE
[/code]

Actually, you have extra/unnecessary parenthesis. All you really need is this:

Code: 
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 >= DateAdd(yy, -3, GetDate()) 
        AND SAMPLEPARAM.PRINT_FLAG=1


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
SWEEEEET!!!!
thanks a bunch

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
312
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
533
BobSuruncle
BobSuruncle
SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
535
SashaBuilder3
SashaBuilder3
gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
558
pjw001
pjw001
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
397
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top