SQL to Coldfusion issue - 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

SQL to Coldfusion issue

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.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

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 MVP
http://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 MVP
http://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 MVP
http://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 MVP
http://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 MVP
http://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 Days

Thanks for your help.

quote:
Originally posted by visakh16

nope.see the () around first part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-30 : 12:11:55

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources