Query by week

June 5, 2019
Newbie 2 posts
Followers: 0 people
2

Query by week

Newbie 2 posts
Followers: 0 people
June 5, 2019

I am converting old Access DB’s to SQL and writing a CF app.  I want to print a report that returns shipments per week.  How can I query a table with a ship date of 06/04/2019 and return everything that needs to be shipped in that week?  The daily WHERE statement is WHERE shipdate = ‘#DateFormat(Now(), “mm/dd/yyyy”)#’

Comments (2)
2019-06-07 23:15:30
2019-06-07 23:15:30

How about

 

 
WHERE CONVERT(date, shipdate) BETWEEN DATEADD(ww, -2, getDate()) AND DATEADD(ww, -1, getDate())

Like
2019-06-07 13:23:29
2019-06-07 13:23:29

You don’t say what your database is, but something like

WHERE shipdate < <cfqueryparam value=”#dateAdd(-1,’w’,now())#” cfsqltype=”datestamp”/>
AND  shipdate > <cfqueryparam value=”#dateAdd(-2,’w’,now())#” cfsqltype=”datestamp”/>

Like
Add your comment