|
| |
|
|
Bnugent
Posts: 257
From: Tampa Florida Tampa, FL USA
Status: offline
 |
SQL Current Month & Year - 1/31/2007 15:40:12
I have tried searching for this answer, thinking it would appear in a lot of posts, and I couldnt find an answer that worked for me...so here goes...
I am currently using this SQL Statement to pull the daily invoices:
SELECT SOP30200.SOPTYPE, SOP30200.SOPNUMBE, SOP30200.DOCDATE, SOP30200.CSTPONBR, SOP30200.CUSTNMBR, SOP30200.CUSTNAME, SOP30200.ShipToName, SOP30200.ADDRESS1, SOP30200.CITY, SOP30200.STATE, SOP30200.ZIPCODE, SOP30200.PHNUMBR1, SOP30200.SUBTOTAL FROM SOP30200 WHERE SOP30200.SOPTYPE = '3' AND convert(varchar(10),SOP30200.DOCDATE,101) = convert(varchar(10),getdate(),101) ORDER BY SOP30200.SUBTOTAL DESC, SOP30200.CUSTNMBR ASC;
I would like to be able to pull 2 more ranges...One being the current Month...The other being the Current year...Can anyone help me here?
Thanks in advance!
_____________________________
Brian---
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: SQL Current Month & Year - 1/31/2007 15:48:09
Month calculation looks like this:
...DATEPART(m, theDateField) AS ItemMonth,...
Year calculations look like this:
...DATEPART(yyyy, theDateField) AS ItemYear,...
Those will return integer values, month being 1 to 12 and year being a 4 digit number. That help?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Bnugent
Posts: 257
From: Tampa Florida Tampa, FL USA
Status: offline
|
RE: SQL Current Month & Year - 1/31/2007 15:50:29
I had tried that...But cant figure out where to change without getting an error...So this line..
quote:
WHERE SOP30200.SOPTYPE = '3' AND convert(varchar(10),SOP30200.DOCDATE,101) = convert(varchar(10),getdate(),101)
What would I change to get it to pull current month instead of current day like it does now?
_____________________________
Brian---
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: SQL Current Month & Year - 1/31/2007 15:53:36
quote:
WHERE DATEPART(m, theDateField) = " & DatePart('m',Date()) & ",...
That any help?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Bnugent
Posts: 257
From: Tampa Florida Tampa, FL USA
Status: offline
|
RE: SQL Current Month & Year - 1/31/2007 16:33:32
I think I am on the right track now. Thanks...But here is whats happending...Here is my new code:
AND DATEPART(m, SOP30200.DOCDATE) = DATEPART(m,GetDate())
Its returning all the sales from January...But its including sales from 2005, 2006 and 2007...I wish to only have sales from the current month of the current year shown...I tried adding:
AND DATEPART(y, SOP30200.DOCDATE) = DATEPART(y,GetDate())
But that didnt work either...
_____________________________
Brian---
| |
|
|
|
BeTheBall
Posts: 6385
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: SQL Current Month & Year - 1/31/2007 17:00:43
You are on the right track by including a clause to deal also with the year. However, the year requires 4 'y's:
AND DATEPART(yyyy, SOP30200.DOCDATE) = DATEPART(yyyy,GetDate())
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Bnugent
Posts: 257
From: Tampa Florida Tampa, FL USA
Status: offline
|
RE: SQL Current Month & Year - 1/31/2007 17:04:53
worked like a charm:
For those of you who may need it:
AND DATEPART(m, SOP30200.DOCDATE) = DATEPART(m, GetDate()) AND DATEPART(yyyy, SOP30200.DOCDATE) = DATEPART(yyyy, GetDate())
Thanks to all who helped!!!!!!
_____________________________
Brian---
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
