Why does using left() on getdate() change it to a different data type?

Question

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!

Answer 1

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.