February 2, 2007 at 10:04 pm
here IS my query:: (At that time i m not making the SP but declate the variable and set this value but i want to amke SP so i make this laterly)
DECLARE @datetest DATETIME
SET @datetest= '12/21/2006'
--PRINT @datetest
select
COUNT(CASE WHEN datepart(hh,@datetest) = 10 THEN dbo.TransactionLineItems.PinID END) AS [10],
COUNT(CASE WHEN datepart(hh,@datetest) = 11 THEN dbo.TransactionLineItems.PinID END) AS [11],
COUNT(CASE WHEN datepart(hh,@datetest) = 12 THEN dbo.TransactionLineItems.PinID END) AS [12],
COUNT(CASE WHEN datepart(hh,@datetest) = 17 THEN dbo.TransactionLineItems.PinID END) AS [17]
from dbo.Transactions INNER JOIN
dbo.TransactionLineItems ON dbo.Transactions.TransactionID = dbo.TransactionLineItems.TransactionID INNER JOIN
dbo.Pins ON dbo.TransactionLineItems.PinID = dbo.Pins.PinID
where TransactionDate = @datetest
Group By CONVERT(VARCHAR(25),TransactionDate,101)
the problem is i want tO pass date AS parameter tO my SP , but whenever i passed the parameter TO my query above,
it shows me nothing.result shows nothing but WHEN i used the same query wiothout passing the parameter it shows the correct result.
plz tell me how can i pass the parameter TO my SP, AS i passed the data parameter AND result according TO that date how many pins selling ON that particular date.
kindly CHECK this AND tell me whats wrong IN this ??
AND other thing IS my query shows the hours OF a particluar date as:
10 11 12 17 ............
is this possible my rsult shows in this format
10 am 11 am 12 am 1 am 2 am ............1 pm 2pm.....
Thanx IN advance. and me wait for good responnse ..
February 5, 2007 at 12:20 am
Without your DDL there will not be much forthcoming.
My best guess, about the parameter not working is that in the example above you use a nice clean date (without a time part). But when you pass the parameter it contains a time part.
In your example, why use VARCHAR(25) when VARCHAR(8) would do just fine?
As for the results you're getting, I suspect the PinID is not what you think it is.
February 5, 2007 at 3:16 am
Also using mm/dd/yyyy as dateformat is risky since it could be dd/mm/yyyy. Safest way is to use yyyymmdd
February 5, 2007 at 10:10 am
What data type is TransactionDate? If it is DATETIME, then it has a time part.
Let's say TransactionDate is 2/6/2007 05:00:00. That is NOT equal (=) to 2/6/2007. Why not? Because SQL Server will add the default time to the date so the comparison becomes:
WHERE TransactionDate = '2/6/2007 00:00:00.000' and they won't match. You can CONVERT the TransactionDate to VARCHAR and just get the date portion.
-SQLBill
February 6, 2007 at 2:42 am
For the AM/PM part of your query you'll have to use a CASE statement; something like:
CASE WHEN datepart(hh,@datetest) > 12
THEN CAST((datepart(hh,@datetest) - 12) AS VARCHAR) + ' pm'
ELSE CAST(datepart(hh,@datetest) AS VARCHAR) + ' am'
END
Ade
February 6, 2007 at 8:17 am
Try:
SELECT CONVERT(VARCHAR(10),GETDATE(),101) + ' ' + SUBSTRING((CONVERT(VARCHAR(19),GETDATE(),100)), 13, 7)
I use GETDATE() for testing purposes. Just replace it with your parameter.
-SQLBill
February 6, 2007 at 10:24 pm
Hey Adrian Nichols, thanx FOR ur reply, i didn't' get ur point , AS IF u see me query i used the comparison::
SELECT
COUNT(CASE WHEN datepart(hh,@datetest)= 10 THEN dbo.TransactionLineItems.PinID END) AS [10 ] -------
IF u see i firstly get the hour FROM variable @datetest THEN compare it WITH 10( 10th Hour) IF i used ur query IN this THEN how
i compare WITH the 10 AND so. AS u see at the END OF this line i used the [10] my COLUMN NAME that displayed IN at the run TIME,
here i get AS this 10 IS 10 am OR pm ??
so kindly give me the complete query. I hope u get my point.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply