| Author |
 Topic  |
|
|
jfm
Posting Yak Master
134 Posts |
 Posted - 05/21/2012 : 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)
Singapore
16745 Posts |
Posted - 05/21/2012 : 07:30:18
|
datediff(month, Exam_Column, Class_Column)
KH
Time is always against us
|
 |
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 05/21/2012 : 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)
Singapore
16745 Posts |
Posted - 05/21/2012 : 08:49:04
|
SELECT datediff(month, Exam_Column, Class_Column) AS Period_Column from Table_1
KH
Time is always against us
|
|
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 05/21/2012 : 09:03:41
|
Perfect! Thanks for your help!
I was misspelling a letter ;-(
|
|
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 05/21/2012 : 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)
Singapore
16745 Posts |
Posted - 05/21/2012 : 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
Time is always against us
|
|
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 05/21/2012 : 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
Time is always against us
|
|
|
| |
Topic |
|
CREATE MONTHS ROUNDING UP
