SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL to Coldfusion issue		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

taunt
Yak Posting Veteran

71 Posts
Posted - 01/24/2013 :  14:37:04  Show Profile  Reply with Quote
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.NSTTL
FROM    Products INNER JOIN
            RunningTotals ON Products.PID = RTotals.PID
WHERE  (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	NSTTL

1	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

India
48076 Posts
Posted - 01/25/2013 :  03:39:30  Show Profile  Reply with Quote
how are you including query inside coldfusion? are you doing any more manipulation at cold fusion end?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

taunt
Yak Posting Veteran

71 Posts
Posted - 01/25/2013 :  11:45:13  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48076 Posts
Posted - 01/26/2013 :  00:30:23  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
Go to Top of Page

taunt
Yak Posting Veteran

71 Posts
Posted - 01/29/2013 :  11:46:29  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/




Hello, isn't that the exact same thing it was?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48076 Posts
Posted - 01/29/2013 :  12:49:24  Show Profile  Reply with Quote
nope.see the () around first part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

taunt
Yak Posting Veteran

71 Posts
Posted - 01/30/2013 :  12:06:00  Show Profile  Reply with Quote
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 Days

Thanks for your help.





quote:
Originally posted by visakh16

nope.see the () around first part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48076 Posts
Posted - 01/30/2013 :  12:11:55  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000