Compare datetime value in stored procedure

Viewed 2513

I am having trouble writing SQL Server queries/procedures with DateTime format in the tables.

My application runs on a standard ASP.NET MVC4 stack with SQL Server.

My table Bookings has this structure:

CREATE TABLE [dbo].[Bookings] 
(
    [BookingId] INT IDENTITY (1, 1) NOT NULL,
    [ShowId]    INT NOT NULL,
    [RowId]     INT NULL,
    [Username]  VARCHAR(100) NULL,
    [PaymentId] INT NULL,
    [ShowDate]  DATETIME NULL,
    .....
    ....    
);

I have written two stored procedures where I am trying to compare table column ShowDate with different date parameters declared in stored procedure.

Procedure #1:

CREATE PROCEDURE [dbo].[GetBookingsByDate]
    @venueid int,
    @fromdate datetime,
    @todate datetime
AS
BEGIN
    SELECT 
        City, Title, ScreenTitle, ShowDate,
        SUM([Qty]) AS Quantity, 
        SUM([Charges]) AS TotalAmount, 
        SUM([OtherCharges]) AS OtherCharges
    FROM 
        ShowBookings
    WHERE 
        Venueid = @venueid 
        AND ShowDate BETWEEN @fromdate AND @todate
    GROUP BY 
        ScreenId, ShowDate, Venueid, Title, ScreenTitle, City
END

Procedure #2:

CREATE PROCEDURE [dbo].[GetAudienceReportsHistory]
    @state varchar,
    @city varchar,
    @theaterName varchar,
    @showdate datetime
AS
BEGIN
    SELECT 
        b.BookingId, b.MobileNo, b.SeatNumbers, b.EmailId,
        sc.ScreenTitle, sh.ShowTime, a.Title, 
        b.Username, b.SMSStatus 
    FROM 
        Bookings b 
    JOIN
        Shows sh ON b.ShowId = sh.Id
    JOIN 
        Venues AS v ON sh.Venue_Id = v.Id
    JOIN 
        Artifacts a ON sh.Artifact_Id = a.Id
    JOIN
        Screens AS sc ON sh.Screen_ScreenId = sc.ScreenId
    WHERE
        b.ShowDate = @showdate 
        AND b.IsBooked = 'true' 
        AND b.TimeSolt = '0' 
        AND v.Title = @theaterName 
        AND v.City = @city
END

As you can see procedure #1 takes two datetime parameters, fromdate and todate. The second procedure takes only one datetime parameter showdate.

Procedure #1 returns the correct set of results, however procedure #2 returns no results at all. But I have crosschecked in the tables that I have proper data which should be returned for the Proc2 query. There seems to be some DateTime format mismatch.

I'm sending datetime parameters to the queries in "yyyy-mm-dd" format (eg: 2017-05-30). Inside the table the ShowDate column is stored in "dd-mm-yyyy" (eg: 30-05-2017) format.

I have tried sending the parameter in different date formats but I'm not getting any results for Proc2. Kindly help me in solving this. Thanks in advance. Let me know if you need more info.

2 Answers

You Need to convert Date Proper Formate like this

CONVERT(date, b.ShowDate) = CONVERT(date,@showdate )
Related