Extracting Records Based on Date (Equal to or Greater then current date)

[SteveSensei]

I have a database table in Microsoft SQL Server that has a smalldatetime field called eventFromDate. I want to write a query that extracts all events if the eventFromDate is equal to or greater then the current date

select eventFromdate from event where eventFromDate => Date()

This will not work and gives me the error message "'date' is not a recognized built-in function name"

[Coyote6]

Probably should be this though I do not know MsSQL like MySQL... I would say post in the other database section as this is where it probably belongs but there is not much activity there. :rolleyes:


SELECT eventFromDate FROM event WHERE eventFromDate>=NOW()

[SteveSensei]

Many thanks for the help, but Now() returns the same error message as the one quoted above in my original posting.

[Old Pedant]

getDate()

It's kind of heresy to suggest this in forums, but...RTFM?

http://msdn.microsoft.com/en-us/library/aa258905(SQL.80).aspx

Now, the problem with that is that it returns the current date *AND TIME*.

So if it is 3:00 PM, no records for today at 10:00 AM will be returned.

Worse, if the records contain date-only values, then no records for today, at all, will be returned (because if there is no time in a DATETIME value, the time is assumed to be 0:00:00).

The only decent way to get the date-only value from a date+time value in T-SQL is this:

CONVERT(DATETIME, CONVERT(VARCHAR, getDate(), 112), 112)

Look here:
http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx

As you can see, 112 is the conversion number for a canonical ISO yyyymmdd date. So you take the date and time, convert it to a *string* of the form 'yyyymmdd' and then convert that string back to a datetime, but now it is the date only.

Isn't SQL Server a royal pain in the patootie??

p.s.: You posted in a MYSQL forum, so you got MYSQL answers. There *is* a separate forum for other DBs.

[Coyote6]

Isn't SQL Server a royal pain in the patootie??


:eek:
Ohhh why did you have to say that!!! I'm about to have to transfer one of my MySQL dbs into an SQL one because of the stupid IT department.... Oh I'm not going to enjoy this am I... :mad:

[Old Pedant]

Heh. SQL Server is almost braindead compared to MySQL and even compared to Access when it comes to date and time and string functions. You can make it all work, but you might find yourself writing some custom UDF (User Defined Functions) just to make future work easier.

For example, you might create DateOnly and TimeOnly UDF's, no?

[Mike_O]

Hey SteveSensei,

Sounds like this should do it for you:
where eventFromDate >= convert(varchar,getdate(),101)

Old Pedant,

How was that painful? Also, regarding this topic of date/time conversion in MSSQL, I've never had any problems with this and neither was I ever forced to create UDF's, and I do stuff like this often.

To try to respectfully veer this forum on the appropriate topic (MySQL), and with you unquestionably being an expert in MySQL, I would like to know how you would improve this condition above.

Mike

[Old Pedant]

MySQL has a function named Date( ) that extracts the date alone from a date time value.

Access has a function named DateValue( ) that does the same thing.

MySQL has CURDATE() and Access has DATE() that give you the current date with no time portion.

I could go on.

It's not that we *can't* do the equivalent in SQL Server, it's just that the SQL Server designers seem to think it is some kind of "feature" to provide their users with the bare bones minimum required functions.

Regarding your answer:

where eventFromDate >= convert(varchar,getdate(),101)

That only works if the default locale for this installation of SQL Server is USA standard. Since you converted the date to MM/DD/YYYY format and now you are asking to compare it to a eventFromDate, a DateTime fileld, one of the other of those two values (the varchar string or the datetime value) must be converted to match the other. By default, I believe, the string will be converted back to a datetime value. If the current locale in SQL Server is, say, UK where the default is MM/DD/YYYY, then you will *not* get a valid match. Your 101 conversion will have produced, say, 7/5/2009 meaning "July 5, 2009" but that will be taken as "7 May 2009" because the server setting is UK standard.

So...

I avoid worrying about what the current default locale is by doing an explicit double conversion. The conversion number can be one of several, so long as you use the same conversion number both ways.

And, of course, since the value needs to be converted back to DateTime, anyways, why not control the conversion rather than relying on a possibly erroneous default conversion?

[Old Pedant]

By the by, I am more familiar with SQL Server than with MySQL, not the other way around. I really do *LIKE* SQL Server. Certainly its Stored Procedures are much easier to use than MySQL (or Oracle, I believe, though my Oracle experience is limited). It's just that we are now on what, the 6th major release of SQL Server? And they still haven't tumbled that adding a few helpful functions such as those mentioned would be a good idea??? Sheesh.

[Mike_O]

Hey Old Pedant,

Sorry for the delayed response. I think I had the Swine Flu or something.

Anyway, point taken on the double conversion. Yes, if I was to go ahead and reinstall SQL Server with something other than USA standard, there would be a many objects I would have to change wherever "convert(varchar,...,101)" is used. However, I still fail to see how even this corrected MSSQL:

where eventFromDate >= CONVERT(DATETIME, CONVERT(VARCHAR, getdate(), 101), 101)

...is any more painful than the following in MySQL...

where eventFromDate >= Date()

Now, if it's a matter of efficiency, then you probably have something there, but as far as syntax goes...well for me it's something way too irrelevent to be writing a custom UDF for, just to save a few characters of code.

Having said that, believe me, I do have some criticism of MSSQL. For example the way their replication works can really screw you up. And yes, like you said, it would certainly be an improvement to have built-in DateOnly and TimeOnly UDF's - they should have done it by now. There's also the feature of "LIMIT" in MySQL, which I found pretty cool (and I wish I had) when I was working on some projects involving pagination. There are certainly other examples I can bring up. But you know it's Microsoft, what can I say. They do this all the time: release a quality product and then over time keep releasing new versions of that product which mostly has some BS added to it. Other than MS, the only worse product I've seen in terms of too many versions is Crystal Reports. I mean, what are they up to like version 37 now? I've stopped counting at version 10.

Putting marketing strategies and whatever else aside, after doing some minor comparisons to the latest MySQL and Access SQL (I did have a bit of exposure to both in the past, but haven't worked with them in years), I just think your criticisms of MSSQL Server such as "Braindead" were a bit strong, even if you're only limiting the topic to date/time/string functions, agree?

Regards,
Mike

[Old Pedant]

There *IS* a VERY NICE way to do the equivalent of LIMIT in SQL Server.

Have to do it in a Stored Proc, but it can be enormously efficient.

Look here:
http://www.4guysfromrolla.com/webtech/042606-1.shtml

I'm pretty sure it even beats MySQL's LIMIT in terms of performance.