I am having a problem adjusting this part of my SQL statement:
HAVING dbo.BOOKINGS.BOOKED = CONVERT(int, CONVERT(datetime,
FLOOR(CONVERT(float, GETDATE()))) + 2)
Normally, the page that uses this statement just lists the amount of sales for today, I want to switch the GETDATE() to a date that I declare. I tried all different formats and none have worked
Use the DATEADD/DATEDIFF method of setting the time portion to midnight of the current date - it's the fastest means, and casting to FLOAT can be unreliable:
HAVING BOOKINGS.dbo.BOOKED = CONVERT(INT, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))+2
Then, you can set your own date easily if you use a variable (@var in this example, within a stored procedure or function):
DECLARE @var DATETIME
SELECT ...
HAVING BOOKINGS.dbo.BOOKED = CONVERT(INT, DATEADD(dd, DATEDIFF(dd, 0, @var), 0))+2
This assumes @var is a DATETIME data type. Otherwise, you'll need to use a date format SQL Server will implicitly convert to a DATETIME -- or use CAST/CONVERT to explicitly convert the value.
if you want you to give your own date you could do this instead of getdate() which gives current system timestamp.
Cast('2010-11-04 13:28:00.000' as datetime)
How about
declare @myDate as datetime
set @myDate = '11/2/2010'
. . .
HAVING dbo.BOOKINGS.BOOKED = CONVERT(int, CONVERT(datetime,
FLOOR(CONVERT(float, @myDate ))) + 2)
That should do it, and it should automatically do the type conversion on your date string used in the set statement, or you could just pass in a datetime parameter if this is in a stored procedure.
