SQL HELP… CONVERT(int, CONVERT(datetime, FLOOR(CONVERT(float, getdate())))

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

Answers


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.


Need Your Help

mod_geoip + Apache 2.2 in fcgid mode

php apache geoip

Is there a way to get mod_geoip working when Apache 2.2 is running in fcgid mode?

Weird behaviour of if(x instanceof X)

java class inheritance instanceof

I have a class PartA and a class PartB which is a derived class of PartA.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.