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!

Meredith

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. – Apus

I understand this may not be the point of the question, but if you want just the date, you can use convert(date,getdate()) – Decurved

Or in SQL Server 2012 and newer, you could use SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd'); to get exactly what you need .... – Encyclopedist

Straight from w3schools, 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. – Gertie

@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. – Gertie

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. – Meredith

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.

Cordillera

This msdn.microsoft.com/en-AU/library/ms187928.aspx says default conversion is 0 rather than it being internationalization settings – Angelineangelique

Shouldn't have been a comment in the fist place :) Nice answer. – Bringingup

@Nick.McDermaid . . . I will refer you to Aaron Bertrand's discussion on this topic: #10399421. – Cordillera

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. – Angelineangelique

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 :) – Meredith

Aaron's answer is about converting strings to Datetime. Not vice versa. The behaviour also differs for the newer datatypes. https://mcmap.net/q/1621212/-how-does-sql-server-determine-the-style-for-convert-when-it-isn-39-t-specified – Proprietress

Answer 2

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

SELECT CONVERT(DATE,GETDATE());

Dealer