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 - 2013-01-24 : 14:37:04
|
Hello I'm running this query:SELECT DATEDIFF(d, Products.ReceiptDate, Products.last_sold) / 365 AS Years, DATEDIFF(d, Products.ReceiptDate, Products.LAST_SOLD) % 365 / 30 AS Months, DATEDIFF(d, Products.ReceiptDate, Products.LAST_SOLD) % 365 % 30 AS Days, Products.UPC, Products.SKU, Products.Attribute1, Products.ProductName, Products.Attribute2, Products.LastOrderDate, Products.ReceiptDate, Products.LAST_SOLD, Products.VendorID, Products.buyqty, Products.StockQty, Products.UsedQty, RTotals.NSTTLFROM Products INNER JOIN RunningTotals ON Products.PID = RTotals.PIDWHERE (Products.VendorID = '1051') AND (Products.bqty > 0)ORDER BY years, months which will return:Years Months Days UPC SKU Artist ProductName format LastOrderDate ReceiptDate LAST_SOLD VendorID bqty StockQty UsedQty NSTTL1 11 27 745316026329 EC263 CULT OF LUNA BEYOND CD 6/1/2010 6/11/2010 6/2/2012 1051 1 0 0 4 Only thing is when I do it in Coldfusion the date format is messed up. Instead of returning 1 11 27 for the years,months, days it will say something like 1 14 15. So it won't add the days correctly. I know this is more a Coldfusion question, but I thought I'd ask here cause someone might know why. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-25 : 03:39:30
|
how are you including query inside coldfusion? are you doing any more manipulation at cold fusion end?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
taunt
Posting Yak Master
128 Posts |
Posted - 2013-01-25 : 11:45:13
|
Here's what I have in Coldfusion:#DATEDIFF("YYYY", qsearch.ReceiptDate, qsearch.last_sold)# Years, #DATEDIFF("d", qsearch.ReceiptDate, qsearch.last_sold) % 365 / 30# Months, #DATEDIFF("d", qsearch.ReceiptDate, qsearch.last_sold)%365%30# Days And that gives me the wrong sold date info. Either the months are off or days (or both).quote: Originally posted by visakh16 how are you including query inside coldfusion? are you doing any more manipulation at cold fusion end?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-26 : 00:30:23
|
can you try like this#DATEDIFF("YYYY", qsearch.ReceiptDate, qsearch.last_sold)# Years, #(DATEDIFF("d", qsearch.ReceiptDate, qsearch.last_sold) % 365) / 30# Months, #(DATEDIFF("d", qsearch.ReceiptDate, qsearch.last_sold)%365)%30# Days------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
taunt
Posting Yak Master
128 Posts |
Posted - 2013-01-29 : 11:46:29
|
quote: Originally posted by visakh16 can you try like this#DATEDIFF("YYYY", qsearch.ReceiptDate, qsearch.last_sold)# Years, #(DATEDIFF("d", qsearch.ReceiptDate, qsearch.last_sold) % 365) / 30# Months, #(DATEDIFF("d", qsearch.ReceiptDate, qsearch.last_sold)%365)%30# Days------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Hello, isn't that the exact same thing it was? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 12:49:24
|
nope.see the () around first part------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
taunt
Posting Yak Master
128 Posts |
Posted - 2013-01-30 : 12:06:00
|
OK that worked a little better the only thing is that this was the result of one example:0 Years, 5.33333333333 Months, 10 Days In sql it's:0 Years,5 Months, 11 DaysThanks for your help.quote: Originally posted by visakh16 nope.see the () around first part------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-30 : 12:11:55
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|