Home Dashboard Directory Help
Search

SQL 2008: Add sysdate() by Erland Sommarskog


Status: 

Active


29
1
Sign in
to vote
Type: Suggestion
ID: 293333
Opened: 8/15/2007 12:11:49 AM
Access Restriction: Public
2
Workaround(s)
view

Description

With the new date/time data types in Katmai, a lot of people will try:

     SELECT * FROM datecol = SYSDATETIME()

but since datecol will be autoconverted to datetime2(7), and there will be
no rows returned.

Yes, convert(date, sysdatetime()) is the simple workaround, but I expect
this to be an extremely common operation, why a sysdate() that returns
date is more than warranted.

It would be logical to add a systime() to make the collection complete, but this
is not equally compelling.
Details
Sign in to post a comment.
Posted by Peter11111 on 11/9/2010 at 6:29 AM
CURRENT_DATE and CURRENT_TIME would be the best names for ANSI standards compliance
Posted by John Hardin on 8/22/2007 at 5:33 PM
> God knows what you should do with the incorrectly defined CURRENT_TIMESTAMP....

Fix it to be standards-compliant?
Posted by Microsoft on 8/19/2007 at 2:29 AM
"And using a tricks with DATEADD that strips sysdatetime/getdate will lead to conversion
of the column and result in poorer performance because of non-optimal index usage."

Do you know that we did add additional support for CAST/CONVERT of new/old date/time types to keep the optimal index seekability in Katmai as part of the new date/time feature?

Using CURRENT_DATE does sound a good suggestion regarding standard compliance.

We didn't choose any timestamp related naming because SQL Server somehow already took it with a type that has nothing to do with date/time although we absolutely wanted (trust me :))
Posted by Erland Sommarskog on 8/17/2007 at 1:24 PM
The absolutely most common scenario is #1 - I want the current date in my time zone. While it
would be nice to have a parameter for UTC or have a sysutcdate(), I think that is overkill.
This can easily be achieved with cast/convert. So can the local date, but this case is
extremely common, and there is a gotcha, or rather two: 1) Using sysdatetime/getdate will not yield any
rows at all 2) And using a tricks with DATEADD that strips sysdatetime/getdate will lead to conversion
of the column and result in poorer performance because of non-optimal index usage.

But I have another request: call the function CURRENT_DATE, without parens to align with the ANSI standard.
(And preferably sysdatetime should be LOCALTIMESTAMP. God knows what you should do with the
incorrectly defined CURRENT_TIMESTAMP....
Posted by AaronBertrand on 8/17/2007 at 5:28 AM
My vote is for 3. then 1. then 2.
Posted by AaronBertrand on 8/17/2007 at 5:27 AM
Geez Festeron, Erland had a typo in his query. Forgive him. It is obvious to me that he meant:

SELECT * FROM table WHERE datecol = SYSDATETIME()
Posted by Microsoft on 8/17/2007 at 1:19 AM
Assuming we're going to add a new built-in for SYSDATE(), which of the following option you would perfer better:
1. Make it no parameter, and return the current system local date to the current system time zone
2. Make it no parameter, and return the UTC date
3. Hava a optional parameter to indicate local or UTC, and make the default to be local.

I would appreciate your input so that we can make good design decision if we're going to enhance it for Katmai.

thanks
Posted by Microsoft on 8/16/2007 at 12:11 AM
Thanks for the great feedback and suggestion.

In addition to the workaround of using CAST/CONVERT (to DATE type) for the predicate, to assign the SYSDATETIME to a DATE variable first will also work against the scenario since we do support implicit conversions for old and new date/time types.

Giving the time/resource constrain and available workarounds, we didn't consider adding other extra sys built-ins like SYSDATE, SYSTIME and etc as the higher priority although they're in our original proposed list.

Depending on the requirement rating, we'll definitely reserver the plan for future support.

thanks
Posted by Festeron on 8/15/2007 at 8:59 AM
I don't understand how

SELECT * FROM datecol = SYSDATETIME()

means anything
Sign in to post a workaround.
Posted by akelly on 8/15/2007 at 7:50 PM
I agree that SYSDATE() is the best solution but if time (pun intended) is a consideration for Katmai why not have an optional parameter that returns a DATE only value?
Posted by Steve Kass on 8/15/2007 at 6:37 AM
I'm not sure why people will try this any more than they will try WHERE [datecol] = GETDATE(), which fails people plenty often now, but I agree that with the new [date] type, yet more people will fall into date comparison traps. A SYSDATE() (or CURRENT_DATE to use a standard name) would be much better than the workaround of WHERE datecol = CAST(GETDATE() AS DATE).