Dear Fellow SQueaLers:

I am continuing with the textbook I have been studying off and
on. I found an interesting error in the date/time functions. The
code is

declare @WhatsTheDay date
set @WhatsTheDay='24 March 2009'
select
datename(dw,datepart(dd,@WhatsTheDay))+', '+
CAST(datepart(dd,@WhatsTheDay) as varchar(2))+' '+
DATENAME(mm,@WhatsTheDay)+' '+
CAST(datepart(yyyy,@WhatsTheDay) as CHAR(4))

It gives a result of "Thursday, 24 March 2009".

The datepart() call for the day of the week looks redundant. I
removed it:

select
datename(dw,@WhatsTheDay)+', '+
CAST(datepart(dd,@WhatsTheDay) as varchar(2))+' '+
DATENAME(mm,@WhatsTheDay)+' '+
CAST(datepart(yyyy,@WhatsTheDay) as CHAR(4))

This gives a result of "Tuesday, 24 March 2009". That date was a
Tuesday.

The text is wrong, but where is the Thursday result coming from?
There is no error thrown.

Sincerely,

Gene Wirchenko

On 2011-02-08 01:26, Gene Wirchenko wrote:
I hope you mean an error in how the textbook uses them, not the functions
themselves. Standard functions/libraries rarely contain gross errors because
they would have been found and fixed by now.

This call resolves to DATENAME(DW, 24). 24, when converted to a DATETIME, is
1900-01-25 00:00:00.000. January 25th, 1900 was a Thursday (in most
countries, anyway).

The implicit conversion between INT and DATETIME is a misfeature that can
easily trip you up, but there's no error in the functions.

--
J.

Hi Gene,

You didn't ask about this, but I hope you are aware that this code
will only run error free if you are using English settings. Change the
language settings to almost any other language, and you get an error
thrown.
The advised way to hardcode dates is to use the YYYYMMDD format:

SET @WhatsTheDay = '20090324';


It's easy to see what goes wrong here if you try the individual parts:

SELECT datepart(dd,@WhatsTheDay)

returns the integer value 24. As expected. Since this is then used in
a place where a datetime arguument is expected, SQL Server will
implicitly convert to datetime:

SELECT CAST(24 AS datetime);

returns Jan 25th, 1900. Which happens to be a thursday.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

On Tue, 08 Feb 2011 07:52:59 +0100, Jeroen Mostert
<jmostert (AT) xs4all (DOT) nl> wrote:

Ouch. Yes, I meant the textbook.

[snip]

I thought it might be that. Misfeature is right.

Sincerely,

Gene Wirchenko