March 1, 2007 at 12:44 am
select Suppliers.CompanyName as Suppliers,
COUNT(CASE WHEN datepart(hh,TransactionDate) = 22 THEN dbo.TransactionLineItems.PinID END) AS [10:00 PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 23 THEN dbo.TransactionLineItems.PinID END) AS [11:00 PM]
FROM dbo.Suppliers INNER JOIN
dbo.Batches ON dbo.Suppliers.SupplierID = dbo.Batches.SupplierID INNER JOIN
dbo.PinPackages ON dbo.Suppliers.SupplierID = dbo.PinPackages.SupplierID INNER JOIN
dbo.Pins ON dbo.Batches.BatchID = dbo.Pins.BatchID AND dbo.PinPackages.PinPackageCode = dbo.Pins.PinPackageCode INNER JOIN
dbo.TransactionLineItems ON dbo.Pins.PinID = dbo.TransactionLineItems.PinID INNER JOIN
dbo.Transactions ON dbo.TransactionLineItems.TransactionID = dbo.Transactions.TransactionID
where TransactionDate >= dbo.FormatDate('12/21/06 12:00:00.000 am')
AND TransactionDate <= dbo.FormatDate('12/21/06 11:59:59.999 pm')
AND Transactions.TransactionTypeID = 3 AND Transactions.TransactionStatusCode = 4
Group By CONVERT(VARCHAR(25),TransactionDate,101), Suppliers.CompanyName
A little problem i have the above query in the where clause i pass the data with time , but now i want to make a SP and
in this i passed the date parameter , but when i write the following lines: it displays nothing.
where TransactionDate>=dbo.FormatDate(@TranDate)
and TransactionDate<=dbo.FormatDate(@TranDate)
i make afunction format date which formars the date format in the follwoing style: d-mmm-yyyy hh:mm:ss am/pm
kindly tell me how do i get this as i want to make SP in which user always enters the and single date paramater value ?
reple me asap.Thans in Advance.
March 1, 2007 at 1:04 am
hey i solved this problem , i used following code:
where TransactionDate >=dbo.FormatDate(@TDate+'12:00:00.000 pm' )
AND TransactionDate <=dbo.FormatDate(@TDate+'11:59:59.999 pm')
now just tell me is this a fine way to do that or not? if there is any other way then kindly tell me.
March 1, 2007 at 9:07 am
Assuming that TransactionDate is a DATETIME column, I'm not sure why you would need to format the parameter differently than what it is (i.e. mm/dd/yy hh:mi:ss.mmm).
Also be aware you may encounter 'rounding' issues when '12/21/06 11:59:59.999 pm' is rounded to '12/27/06 12:00:00.000 am'. 997 is the maximum value maintained in the milliseconds part of a DATETIME. Similar rounding occurs with SMALLDATETIME.
In either case, I would DECLARE two variables of the appropriate datatype (e.g. @startDate and @endDate) and assign the values desired to the variables and use the variables explicitly in the WHERE clause. Using a UDF in the WHERE clause has a tendency to be less efficient.
The safest way (and one that is independent of the column datatypes)
CREATE PROCEDURE dbo.myProc @startDate DATETIME, @endDate DATETIME
SET @startDate = CONVERT(VARCHAR(10), @startDate, 101) -- defaults to midnight
SET @endDate = CONVERT(VARCHAR(10), DATEADD(dd, 1, @endDate), 101 -- midnight the next day
SELECT...
WHERE TransactionDate >= @startDate AND TransactionDate < @endDate
Note the LESS THAN operator in the @endDate comparison!
HTH
March 5, 2007 at 11:25 pm
Any translation of a DATETIME to anything character based or though most UDF's will be slower than doing some simple math using date functions... try this...
WHERE TransactionDate >= DATEADD(dd,DATEDIFF(dd,0,@TDate),0)
AND TransactionDate < DATEADD(dd,DATEDIFF(dd,0,@TDate),0) + 1
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply