SQL 2008: Add sysdate()
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.
AdminMicrosoft SQL Server
(Product Manager, Microsoft Azure)
shared this idea
Upvotes: 35
<=-=Aug 15 2007 8:59AM=-=>I don’t understand how
SELECT * FROM datecol = SYSDATETIME()
means anything
<=-=Aug 16 2007 12:11AM=-=>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
<=-=Aug 17 2007 1:19AM=-=>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
<=-=Aug 17 2007 5:27AM=-=>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()
<=-=Aug 17 2007 5:28AM=-=>My vote is for 3. then 1. then 2.
<=-=Aug 17 2007 1:24PM=-=>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….
“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 :))
<=-=Aug 22 2007 5:33PM=-=>> God knows what you should do with the incorrectly defined CURRENT_TIMESTAMP….
Fix it to be standards-compliant?
<=-=Nov 9 2010 6:29AM=-=>CURRENT_DATE and CURRENT_TIME would be the best names for ANSI standards compliance