7

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!

Gonzalo.-
  • 12,512
  • 5
  • 50
  • 82
S.Beeley
  • 75
  • 1
  • 6
  • I would have expected it to throw an exception to be honest. LEFT is a string function so obviously there is an implicit conversion happening here. – Sean Lange Oct 30 '15 at 14:28
  • I understand this may not be the point of the question, but if you want just the date, you can use `convert(date,getdate())` – Sam Cohen-Devries Oct 30 '15 at 14:31
  • Or in SQL Server **2012** and newer, you could use `SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd');` to get **exactly** what you need .... – marc_s Oct 30 '15 at 14:33
  • Straight from [w3schools](http://www.w3schools.com/sql/sql_func_format.asp), to get the date in any format you want, use something similar to this: `FORMAT(GETDATE(),'YYYY-MM-DD')`, and changing the order and separators of the date fields. You can include Sam's code to stringify it if needed. – CSS Oct 30 '15 at 14:33
  • @Siyual Fixed. Knew it when I posted, but assumed most would recognize that as a MySQL function. A lot can be learned from w3schools, but the best bet is to verify through testing concepts utilizing testing grounds such as (online) sqlfiddle or SQL Server Lite, in the event that you don't have an environment at home or work already set up for you. – CSS Oct 30 '15 at 14:44
  • Thanks everyone. It wasn't so much that I didn't know how to get the date in the correct format, I was half asleep and tried LEFT() and then got curious as to why I got the results I did. – S.Beeley Oct 30 '15 at 14:47

2 Answers2

10

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 2
    This https://msdn.microsoft.com/en-AU/library/ms187928.aspx says default conversion is 0 rather than it being internationalization settings – Nick.Mc Oct 30 '15 at 14:29
  • 1
    Shouldn't have been a comment in the fist place :) Nice answer. – D Stanley Oct 30 '15 at 14:29
  • 1
    @Nick.McDermaid . . . I will refer you to Aaron Bertrand's discussion on this topic: http://stackoverflow.com/questions/10398921/how-does-sql-server-decide-format-for-implicit-datetime-conversion. – Gordon Linoff Oct 30 '15 at 14:35
  • Normally I would bow under both Aaron and your own wisdom but this implies that date>varchar conversion just sticks to one format: `SET DATEFORMAT DMY; select cast(getdate() as varchar); SET DATEFORMAT MDY; select cast(getdate() as varchar);SET LANGUAGE ENGLISH; select cast(getdate() as varchar);SET LANGUAGE US_ENGLISH; select cast(getdate() as varchar);` Happy to be set right. – Nick.Mc Oct 30 '15 at 14:44
  • Thank you for the clarification (between the answer and the comments)! It wasn't so much that I expected this to be the correct way of doing it, I was just curious as to what was causing the different outputs. Answer accepted :) – S.Beeley Oct 30 '15 at 14:46
  • Aaron's answer is about converting strings to Datetime. Not vice versa. The behaviour also differs for the newer datatypes. http://stackoverflow.com/a/3656837/73226 – Martin Smith Nov 01 '15 at 12:16
-1

GETDATE() command returns a DATETIME, you want to return DATE

SELECT CONVERT(DATE,GETDATE());
Tigerjz32
  • 4,324
  • 4
  • 26
  • 34