Sql Date Syntax

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()) )
nitayalevetteAsked:
Who is Participating?
 
wdosanjosConnect With a Mentor Commented:
Sorry, there was a misplaced comma:

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

0
 
Alpesh PatelAssistant ConsultantCommented:
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
0
 
knightEknightCommented:
DateOfOrder BETWEEN convert( datetime, -datediff(day,dateadd(month,-1,getdate()),day(getdate())-1) )
    AND convert( datetime, -datediff(day,getdate(),day(getdate())) )
0
 
knightEknightConnect With a Mentor Commented:
... that is the "user friendly" version - below is shorter and should work just as well:

 DateOfOrder BETWEEN -datediff(day,dateadd(month,-1,getdate()),day(getdate())-1)
    AND -datediff(day,getdate(),day(getdate()))
0
 
wdosanjosCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.