Date Range

Question

Date Range

bpowers

SSCertifiable

Points: 5483
More actions
August 15, 2009 at 2:03 pm

#140962

I am trying to come up with the script to find the number of days an order was open for the following:
Order Create Date = 1/1/07
Order Close Date = 1/1/09
How many days was the order open? Any help will be appreciated.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

DATEDIFF(day, '20070101', '20090101')

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

bpowers SSCertifiable Points: 5483 More actions · Answer 2

bpowers

SSCertifiable

Points: 5483

August 15, 2009 at 2:17 pm

#1039882

Easy enough. Thank you very much.

bpowers SSCertifiable Points: 5483 More actions · Answer 3

How would I get this to work?

SELECT EMP_ID, DATEDIFF(MONTH, START_DATE, END_DATE) AS HISTORY

FROM EMP_HISTORY

WHERE EMP_ID = '253'

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

bpowers (8/15/2009)
How would I get this to work?
SELECT EMP_ID, DATEDIFF(MONTH, START_DATE, END_DATE) AS HISTORY
FROM EMP_HISTORY
WHERE EMP_ID = '253'

DATEDIFF(m, start_date, end_date)

Suggest you look at BOL for DATEDIFF..and test out various date ranges, just to be sure that the results you are getting are what you expect

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

bpowers (8/15/2009)
How would I get this to work?
SELECT EMP_ID, DATEDIFF(MONTH, START_DATE, END_DATE) AS HISTORY
FROM EMP_HISTORY
WHERE EMP_ID = '253'

What doesn't work with the above? You can't just ask us to make something work when you don't even bother to tell us how it isn't working.

I recommend that you read the article I link to in my signature and try posting again.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

bpowers SSCertifiable Points: 5483 More actions · Answer 6

bpowers

SSCertifiable

Points: 5483

August 17, 2009 at 7:08 am

#1040152

I got it working. Thanks.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

bpowers (8/17/2009)
I got it working. Thanks.

Heh... got what working? Did you use Jeff's algo or something else? Two way street here... let us know please. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bpowers SSCertifiable Points: 5483 More actions · Answer 8

The script I posted worked. However, the database I was using did not have any data in it. I was using it for testing purposes and whoever set it up did not populate it with data. I should have checked that first.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

Ah... I hate it when that happens. Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)