ROUND UP LIMITS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ROUND UP LIMITS

Author

Topic

jfm
Posting Yak Master

145 Posts

Posted - 2012-05-22 : 05:02:01

Hi there,

I have a table, with two different columns:

Column_1
31-12-2012

Column_2
09-03-2012

Im using DATEDIFF formula, in order to have in Column_3:

]09-03-2012] - [31-12-2012]

The matter, is that, in my Column_3, when the days are between 01 and 14 DATEDIFF is rounding up to the next month.

So in the example given before the solution of Column_3 is 3 months instead of 2 months.

I just need to differentiate between 01-14 (days) do not roundup and 15-31 roundup.

Any ideas?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-05-22 : 05:25:34

what exactly do you want to have in Column_3?????

No, you're never too old to Yak'n'Roll if you're too young to die.

jfm
Posting Yak Master

145 Posts

Posted - 2012-05-22 : 05:49:33

I want the difference in months, between periods, but:

I just need to roundup my days, if they are between 15 to 31 (days)

20-March = roundup to April
15-March = roundup to April
14-March= rundown to March
09-March = rundown to March

If I use DATEDIFF, he does not take into account, that 09-March is below the day 15, so automatically is rounding-up my period:

DATEDIFF (month, 09-03-2012, 31-12-2011) = 3

And, it has to be equal to 2

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-05-22 : 07:17:07

What's about this:

DATEDIFF(day, '20111231','20120309') / 30

No, you're never too old to Yak'n'Roll if you're too young to die.

jfm
Posting Yak Master

145 Posts

Posted - 2012-05-22 : 08:04:46

No, i tried too.

I have the same answer as before.

I was trying another query:

USE my_database
ALTER TABLE my_table
ADD new_column AS CEILING(DATEDIFF(MONTH, ColumnX, ColumnY)
WHEN My_lookup_column < My_Actual_date

I need to add a condition, in my query which is: "WHEN My_lookup_column < My_Actual_date"

So I need to solve to issues of the query, first the "counting issue', second the "condition' needs to work in the query

Any idea?

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-22 : 10:44:48

Start with the base and then just adjust according to your rules:

SELECT ABS(DATEDIFF(MONTH, '09-03-2012', '31-12-2011')) + CASE WHEN DAY('09-03-2012') < 15 THEN -1  ELSE 0 END

jfm
Posting Yak Master

145 Posts

Posted - 2012-05-22 : 11:04:34

Thanks a lot for the answer.

I need to modify some aspects of the query:

1) I need to create a new_column (with the periods i just calculated) in my existing database.

2) SELECT ABS(DATEDIFF(MONTH, column X, column Y)) + CASE WHEN DAY(either in column X and Y) < 15 THEN -1 ELSE 0 END

How can I do it?

Im sorry, but Im quite lost at this point, I tried many queries and they are not working.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources