[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

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()) )
0
nitayalevette
Asked:
nitayalevette
  • 2
  • 2
2 Solutions
 
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
 
knightEknightCommented:
... 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
 
wdosanjosCommented:
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now