SQL SERVER – Quiz on knowing DATEPART and DATENAME Behaviors

DATEPART and DATENAME accept a string that holds only a time. They fill in the missing date as January 1, 1900. I ran every query of the quiz on SQL Server 2025. The questions stay as I asked them, and the tested answers follow.

Part 1: Understanding DATENAME

The first part of this quiz is understanding what your functions are. Look at the statements below and guess what the answer would be. Then go ahead and try the same inside SQL Server Management Studio.

-- The reason is there is no “Day” specified in the source datetime.
SELECT DATENAME(DAYOFYEAR, '12:12:12.123') [DayOfYear]
SELECT DATENAME(WEEKDAY, '12:12:12.123') [WeekDay]

Should the above code work? What is the output, an error or values? If you did get an error, can you spot the reason? If you got values then can you tell why you got those values?

Part 2: Understanding DATEPART

If you got the above correct, then this should be a breeze for sure. You will get the answer easily. Let me know how many got it right just by watching the statements.

-- What about now?
SELECT DATEPART(weekday, '12:12:12.123') [WeekDay]
SELECT DATEPART(MONTH, '12:12:12.123')[MONTH]

Should the above code work? What is the output, an error or values? If you did get an error, can you spot the reason? If you got values then can you tell why you got those values? Is it different from Part 1 of your guess with DATENAME or same? Can you tell why?
If you got this far, then great. I think you got the basics covered.

Part 3: Bonus Question

I thought it would be interesting to add a twist to the tale and get your guess on what the output should be for the below queries.

-- First
SELECT DATENAME(dayofyear, '1') [DayOfYear]
-- Second
SELECT DATENAME(dayofyear, '1-1') [DayOfYear]
-- Third
SELECT DATENAME(dayofyear, '2:2') [DayOfYear]
-- Fourth
SELECT DATENAME(dayofyear, '23:25') [DayOfYear]
-- Fifth
SELECT DATENAME(dayofyear, '24:25') [DayOfYear]

Now which of the above queries will run without any error?
Let me know via comments to all your guesses and the reasons for those guesses.

The Answer, Tested

I ran each query of the quiz on SQL Server 2025 in its own batch. Parts 1 and 2 return values and no errors.

Both functions get a string with a time and no date. SQL Server converts the string to datetime, and a datetime without a date gets the default date. A cast shows it.

SELECT CAST('12:12:12.123' AS datetime) AS AsDatetime, CAST('2:2' AS datetime) AS TwoTwo, CAST('23:25' AS datetime) AS TwentyThree;

The default date is January 1, 1900. That day is a Monday, the first day of the year and the first month. So DATENAME returns 1 and Monday, and DATEPART returns month 1. Some readers guessed 1753, the lowest datetime date. The cast shows 1900.

Why DATEPART Says 2

Part 2 asked whether the result differs from Part 1. It does, because DATEPART returns a number and the number depends on a session setting. DATEFIRST sets the first day of the week. On this US English server it is 7, so Sunday is 1 and Monday is 2.

SELECT @@DATEFIRST AS DateFirst, DATEPART(weekday, '12:12:12.123') AS WeekDayNumber;
SET DATEFIRST 1;
SELECT @@DATEFIRST AS DateFirst, DATEPART(weekday, '12:12:12.123') AS WeekDayNumber;
SET DATEFIRST 7;

With DATEFIRST 1, Monday is day 1. Ritesh M and ijeb explained the 2 in the comments. Monday is the second day when the week starts on Sunday. Both are right for this setting. DATENAME returns the name Monday in both cases. The setting lasts only for the session, and I set it back to 7 at the end.

The Bonus Question, Tested

Only the third and fourth queries run. Each one returns 1. I ran each query in its own batch, because the five do not run together.

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

That message is what the Messages tab shows for the three failing queries.

Run as the one block above, the batch stops at the first failing statement. SQL Server returns Msg 241 for line 2 and no rows at all. The third and fourth query never show their 1. In SSMS, select one statement at a time, or put GO between them.

The string ‘2:2′ reads as 02:02 and ’23:25′ as 23:25. Both are valid times, so the default date fills in. The string ’24:25’ fails because hour 24 does not exist. The strings ‘1’ and ‘1-1’ are neither a time nor a full date. Add a year and the date works: DATENAME(dayofyear, ‘1-1-2015’) returns 1 on this server.

MKumar, Pushkar and ijeb named the third and fourth query in the comments, and they were right.

What Changes With a Typed Value

The string literal works only because it is a string. Cast the same text to the time type first, and SQL Server refuses.

SELECT DATENAME(dayofyear, CAST('12:12:12.123' AS time));

Msg 9810, Level 16, State 5, Line 1
The datepart dayofyear is not supported by date function datename for data type time.

A time value has no day, so asking it for a day of the year makes no sense. The type tells the truth. The string hides it. Asking the time type for its hour works fine and returns 12.

Is the Default Date a Feature?

You could say the 1900 default is a feature, because the time-only query works. Fair point. It is handy in a demo. In real code it hides a bug: a missing date turns into a Monday in 1900, and no error appears. I would rather get the error.

A Simple Rule

DATEPART and DATENAME are strict about types, so pass typed values, not strings. If the input is a time, keep it as time and ask only for the hour, minute and second. Use DATENAME when you want the weekday name. Use DATEPART(weekday) only when you know the DATEFIRST of the session.

These queries create nothing, so there is nothing to clean up.

A time without a date is not an error, it is January 1, 1900 in disguise.

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