May 10, 2010 at 6:34 am
Hi i have requirement where i need to check the a business DB when the
parameter is getdate() ELSE ArchiveDB but its not checking the businessDB
when i pass the getdate () Please give me some suggestion
Sp Details:
when i pass getdate() its not working and its working when i pass a parameter like
@FDate = '0'
@TDate = '05/10/10' it gives all the records in the Archive DB table
and @Fdate and @Tdate should be nvarchar
DECLARE @FDate NVARCHAR(50)
DECLARE @TDate NVARCHAR(50)
IF @FDate = GETDATE() AND TDate = GETDATE
BEGIN
SELECT
TT.ID,
TT.Price,
TT.Tax,
TT.Backbar,
TT.QtyPurchase,
TT.Discount,
TT.EmployeeID,
FROM
TicketDetails TT
WHERE
TT.Type = 'S'
END
ELSE
BEGIN
SELECT
TT.ID,
TT.Price,
TT.Tax,
TT.Backbar,
TT.QtyPurchase,
TT.Discount,
TT.EmployeeID,
FROM
AB_Reports..TicketDetailsArchive TT
WHERE
TT.Type = 'S'
AND CONVERT(VARCHAR,TT.DateScheduled ,101)>= @FDate
AND CONVERT(VARCHAR,TT.DateScheduled ,101) <= @TDate
END
Thanks
kjkeyan
May 10, 2010 at 6:37 am
GetDate() contains a time portion to , you will need to account for that in your comparison
May 10, 2010 at 6:56 am
kjkeyan (5/10/2010)
Hi i have requirement where i need to check the a business DB when theparameter is getdate() ELSE ArchiveDB but its not checking the businessDB
when i pass the getdate () Please give me some suggestion
Sp Details:
when i pass getdate() its not working and its working when i pass a parameter like
@FDate = '0'
@TDate = '05/10/10' it gives all the records in the Archive DB table
and @Fdate and @Tdate should be nvarchar
DECLARE @FDate as NVARCHAR(50)
DECLARE @TDate as NVARCHAR(50)
IF @FDate = GETDATE() AND TDate = GETDATE
BEGIN
SELECT
TT.ID,
TT.Price,
TT.Tax,
TT.Backbar,
TT.QtyPurchase,
TT.Discount,
TT.EmployeeID,
FROM
TicketDetails TT
WHERE
TT.Type = 'S'
END
ELSE
BEGIN
SELECT
TT.ID,
TT.Price,
TT.Tax,
TT.Backbar,
TT.QtyPurchase,
TT.Discount,
TT.EmployeeID,
FROM
AB_Reports..TicketDetailsArchive TT
WHERE
TT.Type = 'S'
AND CONVERT(VARCHAR,TT.DateScheduled ,101)>= @FDate
AND CONVERT(VARCHAR,TT.DateScheduled ,101) <= @TDate
END
Thanks
kjkeyan
Couple of things. One, if there is an index on AB_Reports..TicketDetailsArchive.DateScheduled, you won't use it because of the conversion to VARCHAR on the column. Two, what are the values normally passed in FDate and TDate? I'm assuming it is a data range such as '2010-01-01' and '2010-01-31' for the month of January. Could you post the entire stored procedure?
May 10, 2010 at 11:17 pm
Sure This is my Sp and i need Check the BusinessBD If the @FDate
and @TDate is equal to GETDATE() else i need to bring the data from
ReportDB but am unable to get data form businessdb if i pass the current date.
--EXEC CLI_GET_ServiceTota101 'ADMIN','0','00:00','23:59','05/11/2010','05/11/2010'
ALTER PROC [dbo].[CLI_GET_ServiceTota101]
(
@BranchID NVARCHAR(50),
@EmployeeID NVARCHAR(50),
@STime NVARCHAR(50),
@ETime NVARCHAR(50),
@FDate NVARCHAR(50),
@TDate NVARCHAR(50)
)
AS
BEGIN
BEGIN
IF @BranchID = 'ADMIN' AND @EmployeeID = '0' AND @FDate = CONVERT(VARCHAR,GETDATE()) AND @TDate = CONVERT(VARCHAR,GETDATE())
BEGIN
SELECT
SD.ServiceName,
TT.ID,
TT.Price,
TT.Tax,
TT.Backbar,
TT.QtyPurchase,
TT.Discount,
TT.EmployeeID,
ED.EmployeeName,
TD.BranchID,
BD.BranchName
FROM
TicketDetails TD
LEFT JOIN TicketTransaction TT
ON TT.TicketID = TD.TicketID
LEFT JOIN ServiceDetails SD
ON TT.ID = SD.ServiceID
LEFT JOIN EmployeeDetails ED
ON TT.EmployeeID = ED.EmployeeID
LEFT JOIN BranchDetails BD
ON TD.BranchID = BD.BranchID
WHERE
TT.Type = 'S'
AND TD.Status= 1
ANDCONVERT(VARCHAR,TD.DateScheduled,108)>=CONVERT(VARCHAR,@STime,108)
AND CONVERT(VARCHAR,TD.DateScheduled,108)<=CONVERT(VARCHAR,@ETime,108)
AND CONVERT(VARCHAR,TD.DateScheduled,101) >=@FDate
AND CONVERT(VARCHAR,TD.DateScheduled,101) <=@TDate
SELECT
SUM(TT.Price)[PriceTotal],
CONVERT(DECIMAL(18,2),SUM(TT.Price*(TT.Tax/100)))[TotalTax],
CONVERT(DECIMAL(18,2),SUM(TT.Price*(TT.BackBar/100)))[TotalBackBar],
CONVERT(DECIMAL(18,2),SUM(TT.Price*(TT.Discount/100)))[TotalDisCount]
FROM
TicketDetails TD
LEFT JOIN TicketTransaction TT
ON TT.TicketID = TD.TicketID
LEFT JOIN ServiceDetails SD
ON TT.ID = SD.ServiceID
LEFT JOIN EmployeeDetails ED
ON TT.EmployeeID = ED.EmployeeID
LEFT JOIN BranchDetails BD
ON TD.BranchID = BD.BranchID
WHERE
TT.Type = 'S'
AND TD.Status= 1
ANDCONVERT(VARCHAR,TD.DateScheduled,108)>=CONVERT(VARCHAR,@STime,108)
AND CONVERT(VARCHAR,TD.DateScheduled,108)<=CONVERT(VARCHAR,@ETime,108)
AND CONVERT(VARCHAR,TD.DateScheduled,101) >=@FDate
AND CONVERT(VARCHAR,TD.DateScheduled,101) <=@TDate
END
ELSE IF @BranchID = 'ADMIN' AND @EmployeeID = '0' AND @FDate <> CONVERT(VARCHAR,GETDATE()) AND @TDate <> CONVERT(VARCHAR,GETDATE())
BEGIN
SELECT
SD.ServiceName,
TT.ID,
TT.Price,
TT.Tax,
TT.Backbar,
TT.QtyPurchase,
TT.Discount,
TT.EmployeeID,
ED.EmployeeName,
TD.BranchID,
BD.BranchName
FROM
AB_Reports..TicketDetailsArchive TD
LEFT JOIN AB_Reports..TicketTransactionArchive TT
ON TT.TicketID = TD.TicketID
LEFT JOIN ServiceDetails SD
ON TT.ID = SD.ServiceID
LEFT JOIN EmployeeDetails ED
ON TT.EmployeeID = ED.EmployeeID
LEFT JOIN BranchDetails BD
ON TD.BranchID = BD.BranchID
WHERE
TT.Type = 'S'
AND TD.Status= 1
ANDCONVERT(VARCHAR,TD.DateScheduled,108)>=CONVERT(VARCHAR,@STime,108)
AND CONVERT(VARCHAR,TD.DateScheduled,108)<=CONVERT(VARCHAR,@ETime,108)
AND CONVERT(VARCHAR,TD.DateScheduled,101) >=@FDate
AND CONVERT(VARCHAR,TD.DateScheduled,101) <=@TDate
SELECT
SUM(TT.Price)[PriceTotal],
CONVERT(DECIMAL(18,2),SUM(TT.Price*(TT.Tax/100)))[TotalTax],
CONVERT(DECIMAL(18,2),SUM(TT.Price*(TT.BackBar/100)))[TotalBackBar],
CONVERT(DECIMAL(18,2),SUM(TT.Price*(TT.Discount/100)))[TotalDisCount]
FROM
AB_Reports..TicketDetailsArchive TD
LEFT JOIN AB_Reports..TicketTransactionArchive TT
ON TT.TicketID = TD.TicketID
LEFT JOIN ServiceDetails SD
ON TT.ID = SD.ServiceID
LEFT JOIN EmployeeDetails ED
ON TT.EmployeeID = ED.EmployeeID
LEFT JOIN BranchDetails BD
ON TD.BranchID = BD.BranchID
WHERE
TT.Type = 'S'
AND TD.Status= 1
ANDCONVERT(VARCHAR,TD.DateScheduled,108)>=CONVERT(VARCHAR,@STime,108)
AND CONVERT(VARCHAR,TD.DateScheduled,108)<=CONVERT(VARCHAR,@ETime,108)
AND CONVERT(VARCHAR,TD.DateScheduled,101) >=@FDate
AND CONVERT(VARCHAR,TD.DateScheduled,101) <=@TDate
END
END
END
Thanks & Regards
kjkeyan
May 12, 2010 at 2:04 am
Hi,
Very easy, use this statement to check what your SQL is returning you
select CONVERT(VARCHAR,GETDATE())
then compare it with you @FDate parameter which is nVarchar. If these two don't match, then you if condition will not work.
Or else
convert the date to a specific format before comparing. like
select CONVERT(VARCHAR,GETDATE(),103), which should return date in dd/mm/yyyy format.
refer to the below link to know the codes for explicit cast/convert to date formats
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply