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.
| Author |
Topic |
|
taunt
Posting Yak Master
128 Posts |
 Posted - 2012-01-12 : 17:18:28
|
| OK here's my query:SELECT DATEDIFF(YEAR, ReceiptDate, LAST_SOLD) AS Years, DATEDIFF(MONTH, ReceiptDate, LAST_SOLD) AS MonthsFROM Products WHERE (VID = '1176') AND (bqty > 0)What I would like to return is a result that's like this if my result is 25 months it would display 2 years 1 month. Right now I get 2 years 25 months. So how can I format it to take the months off the year and display the remaining months? Thanks for any help. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-01-12 : 17:30:11
|
There are division and modulos operators that you can use. For example:SELECT 25 / 12 AS Years, 25 % 12 AS Months |
 |
|
|
taunt
Posting Yak Master
128 Posts |
Posted - 2012-01-12 : 17:36:51
|
| Sorry there are several results. I would like to know what would be the best way to display them in the format like that.SELECT 25 / 12 AS Years, 25 % 12 AS MonthsWould work on 1 result, but what if one is 15 months and one is 23 months. I would like them to display as 1 year 3 months for the first and 1 year, 11 months for the second. I didn't know if there was a better way to get it display how I want it to. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
taunt
Posting Yak Master
128 Posts |
Posted - 2012-01-12 : 19:05:43
|
| Yep that what I was the thing I originally looked at. I think that diffdate doesn't work correct cause this is one result I get start 10/22/2010 end 6/24/2011 = 1 years, 8 months. That was using the original DATEDIFF(YEAR, Products.ReceiptDate, Products.LAST_SOLD) AS Years, DATEDIFF(MONTH, Products.ReceiptDate, Products.LAST_SOLD) AS Months. So I duno. Kind of odd. |
|
|
|
|
|
|
|
|
DATEDIFF issues