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.