• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

get data only for current month

I have a table with orders data that has the next 3 columns:

sched_start_date
sched_finish_date
desired_ship_date


sample data (same order as above):

2012-04-25 00:00:00      2012-04-26 00:00:00      2012-04-24 00:00:00.000
2012-04-26 00:00:00      2012-04-30 00:00:00      2012-04-27 00:00:00.000
2012-04-30 00:00:00      2012-04-30 00:00:00      2012-04-27 00:00:00.000
2012-05-03 00:00:00      2012-05-03 00:00:00      2012-05-01 00:00:00.000
2012-05-04 00:00:00      2012-05-04 00:00:00      2012-05-01 00:00:00.000
2012-05-07 00:00:00      2012-05-08 00:00:00      2012-05-01 00:00:00.000
2012-05-08 00:00:00      2012-05-09 00:00:00      2012-05-08 00:00:00.000
2012-05-09 00:00:00      2012-05-09 00:00:00      2012-05-04 00:00:00.000


that determines whether an order will ship late.
I would like to retrieve only the orders that are going to be late for the current month since as right now, all months are being retrieved. Just need to retrieve the late orders for the current month

Is this something I could get help with? I am using MS SQL Server 2000

Thank you much.
0
metropia
Asked:
metropia
  • 4
  • 3
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
add this to your where clause:

-- current month
datepart(mm, desired_ship_date) = datepart(mm,getdate())
and

-- is late?
sched_finish_date > desired_ship_date
0
 
metropiaAuthor Commented:
actually i take that back, can it look to the last 30 days, using current date as stating point?

thanks
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
-- last 30 days.
desired_ship_date > dateadd(d, getdate(), -30)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
metropiaAuthor Commented:
I have it like this:

 
SELECT * FROM RG_ORDERS_THAT_MAY_GO_LATE_REPORT_VW
WHERE DATEPART(mm, DESIRED_SHIP_DATE) = DATEPART(mm,GETDATE())
and desired_ship_date > dateadd(d, getdate(), -30)


but I get an error:

Msg 8116, Level 16, State 1, Line 1
Argument data type datetime is invalid for argument 2 of dateadd function.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
sorry, I inversed:

-- 30 days ago, at midnight.
desired_ship_date >= dateadd(dd,datediff(dd,0,Getdate())-30,0)
and
--one second before midnight for shipments later today
desired_ship_date  <= (dateadd(dd, datediff(dd,0,Getdate()),0)- 1)+ '23:59:59'
0
 
metropiaAuthor Commented:
would you be able to explain to me what the new clauses are doing so that i can better understand?

Thanks a lot.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
there was actually a slight error:

use these:


-- 30 days ago, at midnight.
desired_ship_date >= dateadd(dd,datediff(dd,0,Getdate())-30,0)
and

--one second before midnight for shipments later today
desired_ship_date  <= (dateadd(dd, datediff(dd,0,Getdate()),0))+ '23:59:59'


Run this to get back dates:

SELECT     DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) - 30, 0) AS minDate, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) + '23:59:59' AS maxDate



The reason for these are because of time stamps.  

If you run the query at 15:00 and there is a later shipdate today (say 17:00) you won't pull that in your query.

to get around that we set the time to midnight of the minDate and and 23:59:59 of the max date to pull back everything in those dates.
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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