CREATE MONTHS ROUNDING UP - 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

CREATE MONTHS ROUNDING UP

Author

Topic

jfm
Posting Yak Master

145 Posts

Posted - 2012-05-21 : 07:24:52

Hi there,

I have to different columns inside my Table_1. Both columns are dates.

what I need is to see the difference of months between dates, rounding up the solution.

Imagine:

Class_Column Exam_Column

2012-05-28 2012-01-28

Answer in a NEW_Column = 4 (months)

I did try the structure from W3Schools site, but still not working.

Any clue?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-21 : 07:30:18

[code]datediff(month, Exam_Column, Class_Column)[/code]

KH
[spoiler]Time is always against us[/spoiler]

jfm
Posting Yak Master

145 Posts

Posted - 2012-05-21 : 08:40:40

Hi,

That one, is the one that is not working in my case:

msg 102, level 15, state 1, line 1
incorrect syntax near 'month'

datediff(month, Exam_Column, Class_Column)

And i tried:

datediff(month, Exam_Column, Class_Column) from Table_1 into Period_Column

not working

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-21 : 08:49:04

[code]
SELECT datediff(month, Exam_Column, Class_Column) AS Period_Column from Table_1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

jfm
Posting Yak Master

145 Posts

Posted - 2012-05-21 : 09:03:41

Perfect! Thanks for your help!

I was misspelling a letter ;-(

jfm
Posting Yak Master

145 Posts

Posted - 2012-05-21 : 09:38:11

How could I add this Period_Column to Table_1? That way I will have the Period_Column information in the same table

SELECT datediff(month, Exam_Column, Class_Column) AS Period_Column from Table_1

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-21 : 09:41:04

2 way.
1. as a normal column
- you need to update this column value manually
2. as a computed column
- the value is auto computed whenever the value of the 2 other column changes

Normal Column
-------------
alter table Table_1 add Period_Column integer

Computed Column
---------------
alter table Table_1 add Period_Column as datediff(month, Exam_Column, Class_Column)

KH
[spoiler]Time is always against us[/spoiler]

jfm
Posting Yak Master

145 Posts

Posted - 2012-05-21 : 12:13:23

That part is working perfectly.

Other issue that I found is that, the formula is rounding up, whenever my date day is bigger than 01, for instance:

Party_Column Bday_Column

09/03/2012 31/12/2011

Output given in Terms_Column, using the formula Datediff:

Terms_Column
3

In the reality, It has to be 2 terms, I want to use the condition that If any of my columns has a day between (01-14), do not round up my date, so I will have as an output:

Terms_Column
2

And if my day is between (15-31) round up.

how can I use Ceiling in the formula:

ALTER TABLE X
ADD Terms_Column AS DATEDIFF (MONTH, Party_Column, Bday_Column)

Many thanks

quote:
Originally posted by khtan

2 way.
1. as a normal column
- you need to update this column value manually
2. as a computed column
- the value is auto computed whenever the value of the 2 other column changes

Normal Column
-------------
alter table Table_1 add Period_Column integer

Computed Column
---------------
alter table Table_1 add Period_Column as datediff(month, Exam_Column, Class_Column)

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources