Change in Date Format: Why LEFT Turns GETDATE Into Text

A change in date format surprises people the first time LEFT meets GETDATE. One short query shows the same moment in two different layouts. The cause is a conversion you never wrote.

The Puzzle

Run the query below. The first column is the current date and time. The second column cuts the first 11 characters from the left of that same value. You would expect the second column to show the front part of the first one.

SELECT GETDATE() AS RawValue, LEFT(GETDATE(), 11) AS LeftEleven;

The time changes on every run. The first column reads year, month, day, then the time. The second reads month name, day, year, and no time. The time is gone because 11 characters is all the second column keeps. The order of the parts changed as well. The puzzle is this: what causes the change in date format from yyyy-mm-dd to mon dd yyyy? Try an answer before you read on.

The Answer: LEFT Works on Text

LEFT is a string function. It takes characters from the left side of a string. GETDATE returns a datetime value, which isn’t a string. So SQL Server converts the datetime to text first, and then LEFT cuts that text. You didn’t write the conversion, and you can’t choose its format. It is an implicit conversion.

For a datetime value the default style is 0. It prints the month name, the day, the year and the time on a 12-hour clock. The next script uses a fixed value, so you can repeat it on any day. It shows the raw value, the full converted text and the 11 characters that LEFT keeps.

DECLARE @Now datetime = '2026-10-06T19:21:06.513';
SELECT @Now AS RawValue, CONVERT(varchar(30), @Now) AS AsText, LEFT(@Now, 11) AS LeftEleven;

The first column was never text. A datetime value has no format of its own. Client tools such as SSMS and sqlcmd draw it in year-month-day order, and that layout belongs to the client. The third column is the front of the second one, exactly as LEFT promised. Style 0 is the format that changed the look.

Why 11 characters? The day sits in a field two characters wide, so a single-digit day gets a space in front. Both fit: Oct  6 2026 and Oct 19 2026 are 11 characters each. So LEFT keeps the whole date part for any day, and that is why the puzzle looks so tidy.

Language Changes the Text

The implicit conversion uses the language of your session. SET LANGUAGE changes it for the current connection only. The next script runs the same LEFT call under three settings, and then tries SET DATEFORMAT. It ends by setting the language back.

DECLARE @Now datetime = '2026-10-06T19:21:06.513';
SET LANGUAGE German;
SELECT LEFT(@Now, 11) AS German;
SET LANGUAGE Dutch;
SELECT LEFT(@Now, 11) AS Dutch;
SET LANGUAGE us_english;
SET DATEFORMAT dmy;
SELECT LEFT(@Now, 11) AS DayFirst;
SET DATEFORMAT mdy;

German gives Okt, and Dutch gives okt in lower case. SET DATEFORMAT changed nothing. That setting only tells SQL Server how to read text as a date. Style 0 prints text in a fixed shape. So the same query returns a different string on a server with another language. Any change in date format like this is a reason to avoid the implicit conversion.

Newer Date Types Behave Differently

The datetime and smalldatetime types convert to text with style 0. The newer types, date and datetime2, convert to the ISO layout, year first. The next query puts square brackets around the text, so you can see every character.

DECLARE @Now2 datetime2 = '2026-10-06T19:21:06.5130000', @Day date = '2026-10-06';
SELECT '[' + LEFT(@Now2, 11) + ']' AS Datetime2Text, '[' + LEFT(@Day, 11) + ']' AS DateText;

The datetime2 value keeps year-month-day order, and the 11th character is the space before the time. The date value has only 10 characters, so LEFT returns all of them. The puzzle exists because GETDATE returns the older datetime type. Switch to SYSDATETIME and the symptom disappears, but the habit is still risky.

Get the Date You Want on Purpose

If you only need the date, convert to the date type. Keep the value as a date, and no text is involved. If you need text, ask for a style by number. Style 23 is the ISO layout, year-month-day. FORMAT works as well, and it is handy for one value. Over many rows, FORMAT is slower than CONVERT.

SELECT CONVERT(date, GETDATE()) AS DateType,
       CONVERT(char(10), GETDATE(), 23) AS IsoText,
       FORMAT(GETDATE(), 'yyyy-MM-dd') AS FormatText;

You could argue that LEFT(GETDATE(), 11) is fine because it looks right on your server. It looks right until the session language changes, or the column becomes datetime2. Then the text changes under you. Text dates also sort by their letters. Sorted as text, December comes before January, so the months land in the wrong order.

What to Remember

A change in date format after a string function is a sign of an implicit conversion. LEFT, RIGHT, SUBSTRING and similar functions all want text. When you hand them a datetime, SQL Server converts it and picks the style for you.

Convert on purpose. Use the date type to drop the time, and a numbered style when you need text. Then the result doesn’t depend on the language of whoever runs the query.

A date is not a string, it is a value that becomes one only when you say how.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.