Menu
Running two simple select statements:
SELECT GETDATE()
SELECT LEFT(GETDATE(), 10)
Returns:
2015-10-30 14:19:56.697
Oct 30 201
I was expecting LEFT() to give me 2015-10-30, but instead it does not.
Does anyone know why? Is it to do with the style of the data type GETDATE returns?
Thanks!
525
Description. GETDATE returns the current local date and time for this timezone as a timestamp; it adjusts for local time variants, such as Daylight Saving Time. GETDATE can return a timestamp in either %TimeStamp data type format (yyyy-mm-dd hh:mm:ss.
The Oracle and MySQL equivalent of GETDATE is SYSDATE.
CREATE OR ALTER FUNCTION [dbo]. GetDate RETURNS datetime2 AS BEGIN RETURN ISNULL((SELECT CurrentMoment FROM dbo. System), SYSDATETIME()); END GO -- Yes the above returns a more accurate clock than GETDATE(). Replace ALL references to GETDATE() with dbo.
GETDATE() returns a datetime value. When you do SELECT GETDATE(), then the application is getting a datetime value and figuring out how to display it. The application you are using is wisely choosing an ISO-standard format.
When you do LEFT(GETDATE(), then the database needs to do an implicit conversion from datetime to some string value. For this, it uses its internationalization settings. What you are seeing is based on these settings.
Moral of the story: avoid implicit conversions. Always be explicit about what you are doing, particularly in SQL which has rather poor diagnostic capabilities. So, use CONVERT() with the appropriate format for what you want to do.
191
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
