We help IT Professionals succeed at work.

Sql Date Syntax

Medium Priority
246 Views
Last Modified: 2012-05-11
I am using the below sql syntax to pull data for the previous month, and its not pulling all data for the month.  How can I adjust the below syntax?

DateOfOrder BETWEEN dateadd(month, -1, (dateadd ( day, -(DATEPART(day, getdate()))+1, getdate() )))
    AND (dateadd(day,-(DATEPART(day,getdate()) ),getdate()) )
Comment
Watch Question

Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
It comes with Time stamp. so please remove timestamp from the Query.

Result of Query

(No column name)      (No column name)
2011-03-01 19:31:07.500      2011-03-31 19:31:07.500
CERTIFIED EXPERT

Commented:
DateOfOrder BETWEEN convert( datetime, -datediff(day,dateadd(month,-1,getdate()),day(getdate())-1) )
    AND convert( datetime, -datediff(day,getdate(),day(getdate())) )
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Top Expert 2011

Commented:
Please try the following:

CAST(DateOfOrder as date) BETWEEN dateadd(month, -1, dateadd(day, 1 - DAY(getdate()), CAST(getdate() as date))),
                              AND dateadd(day, -DAY(getdate()), CAST(getdate() as date))

Open in new window

Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.