Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

weekly scheduled job - SQL syntax

Hello experts

I have got a SQL statement to fetch last weeks web orders.

I have it scheduled it to run every Friday morning at 6 am.

I think I would need slight modification in the SQL to include date and time.

For an instance:

On 24/04/2009 I would expect all the orders between 17/04/2009 00:00 and 23/04/09 23:59

The SQL I have now seems to be returning orders between 17/04/2009 00:00 and 23/04/09 06:00

And on the following week I would get orders data between 24/04/2009 00:00 and 30/04/09 06:00

I am missing all Thursday orders done between 6 am until midnight.

Please can someone advise the change I would need in my SQL statement to include date and time.

Thanks
s
0
newbie27
Asked:
newbie27
  • 3
  • 3
1 Solution
 
newbie27Author Commented:


SELECT     *
FROM         dbo.[Order] INNER JOIN
                      dbo.OrderDetail ON dbo.[Order].OrderID = dbo.OrderDetail.OrderID INNER JOIN
                      dbo.Customer ON dbo.[Order].CustID = dbo.Customer.CustID INNER JOIN
                      dbo.OrderHistory ON dbo.[Order].OrderID = dbo.OrderHistory.OrderID INNER JOIN
                      dbo.ProductCategory ON dbo.OrderDetail.OrderLineProdID = dbo.ProductCategory.ProdID INNER JOIN
                      dbo.Category ON dbo.ProductCategory.CatID = dbo.Category.CatID
WHERE     (dbo.OrderHistory.StatusID = 'ORDER_SENT') AND (dbo.[Order].OrderDate >= DATEADD(dd, - 7, CAST(CONVERT(VARCHAR(10), GETDATE(), 120) 
                      AS DATETIME))) AND (dbo.[Order].OrderDate < GETDATE() - 1)

Open in new window

0
 
pivarCommented:
Hi,

Try this

/peter




SELECT     *
FROM         dbo.[Order] INNER JOIN
                      dbo.OrderDetail ON dbo.[Order].OrderID = dbo.OrderDetail.OrderID INNER JOIN
                      dbo.Customer ON dbo.[Order].CustID = dbo.Customer.CustID INNER JOIN
                      dbo.OrderHistory ON dbo.[Order].OrderID = dbo.OrderHistory.OrderID INNER JOIN
                      dbo.ProductCategory ON dbo.OrderDetail.OrderLineProdID = dbo.ProductCategory.ProdID INNER JOIN
                      dbo.Category ON dbo.ProductCategory.CatID = dbo.Category.CatID
WHERE     (dbo.OrderHistory.StatusID = 'ORDER_SENT') AND (dbo.[Order].OrderDate >= DATEADD(dd, - 7, CAST(CONVERT(VARCHAR(10), GETDATE(), 120) 
                      AS DATETIME))) AND (dbo.[Order].OrderDate < CAST(CONVERT(VARCHAR(10), GETDATE(), 120) AS DATETIME))

Open in new window

0
 
newbie27Author Commented:
hello peter

thanks for your input

please can you explain what this statement actually does?

(dbo.[Order].OrderDate < CAST(CONVERT(VARCHAR(10), GETDATE(), 120) AS DATETIME))

thank you

s
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
RiteshShahCommented:
I guess, Peter intend to convert current date and time to your default date and time which should be yyyy-mm-dd hh:mm:ss
0
 
pivarCommented:
Yes it converts current date to datetime with no time, that is 2009-04-28 00:00:00, and checks if orderdate is less than this datetime. So it will be true for orderdates <= 2009-04-27 23:59:59:9... (or < 2009-04-28 00:00:00)
0
 
pivarCommented:
So this should return the orders as you requested.
If current date is 24/04/2009 the check
(dbo.[Order].OrderDate < CAST(CONVERT(VARCHAR(10), GETDATE(), 120) AS DATETIME))
would evaluate to
(dbo.[Order].OrderDate < '24/04/2009 00:00:00')
0
 
newbie27Author Commented:
thanks for your help peter, much appreciated
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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