March 8, 2011 at 12:57 pm
I would like to pull records from a table
where that the closedate is within the last 30 days(of each day)
select records from tbla
where status = 'c'
and closedate between getdate() -30 and getdate ?
I guess I'm not getting anything is because the timestamp of the date field is not within the range?
March 8, 2011 at 1:26 pm
is column closedate a datetime datatype?
if it is not, you will have to do something like this;
where [closedate] > CONVERT(VARCHAR(10),getdate()-30,121)
and [closedate] < CONVERT(VARCHAR(10),getdate(),121)
or
where [closedate] > CONVERT(VARCHAR(10),getdate()-30,111)
and [closedate] < CONVERT(VARCHAR(10),getdate(),111)
depending on the way the column looks...
March 8, 2011 at 1:40 pm
closedate datetype is 'datetime'.
March 8, 2011 at 2:12 pm
The logic work but I had to change "-30" to "-60" to get within the last 30 days, I wondere why?
March 8, 2011 at 2:37 pm
kd11 (3/8/2011)
The logic work but I had to change "-30" to "-60" to get within the last 30 days, I wondere why?
:blink:
CloseDate between GETDATE() -30 and GETDATE() should get the last 30 days, as long as CloseDate is DATETIME.
Can you RDC to your server and doublecheck the system clock? I wonder if it's set a month ahead.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 8, 2011 at 2:46 pm
Craig Farrell (3/8/2011)
kd11 (3/8/2011)
The logic work but I had to change "-30" to "-60" to get within the last 30 days, I wondere why?:blink:
CloseDate between GETDATE() -30 and GETDATE() should get the last 30 days, as long as CloseDate is DATETIME.
Can you RDC to your server and doublecheck the system clock? I wonder if it's set a month ahead.
heh, DBCC TIMEWARP strikes again...
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply