Bizzare error - datasource and getdate not returning correct day of week

Adam, thanks for catching the typo; I had to type in the SQL statement as I was working between two computers (one for VPN).

We wrote the script as you mentioned; that is exactly two SQL statements, one to "test123" and the other to "test456". Both return the correct timestamp and language, with just a fraction of millisec difference.

Now, to that SQL statement I added the field 'y', which is dayofyear and does not depend on DATEFIRST. Both return the correct value! So the problem seems to be in DATEFIRST.

Could it be possible to have more than one row of DATEFIRST in the MS SQL system table? Or perhaps a damaged access path or index to the system table is causing this?

> The query has been running for years and suddenly it gives me the wrong Day of
> week and First day of week, but the timestamp is correct! The result of the
> calculation for the Day of Week and First day of week suddenly became one day
> ahead.
>
> Here is the code:
>
> <cfquery name="dte0" datasource="test123"
> SELECT getdate() dte, DATEPART(dw, getdate()) dteDW, $$DATEFIRST dteFirst,
> @@language LanguageVersion
> </cfquery>
> <cfdump expand="Yes" label="Query Dump" var="#dte0">

When CF encouters the string within a <cfquery> tag, all it does is resolve
any CF expressions (there are none in your example), and then passes the
finalised string to the DB and patiently waits for the DB to respond, at
which point CF converts the DB-specific result set to a CF recordset, and
returns it to the calling code. There is very little involvement on the
part of CF other than that: the DB does most of the work, and is providing
all the data.

All that said, it does seem weird that one DSN returns one thing, and
another returns something else. Is your test rig like this:

<cfquery name="dte0" datasource="test123"
SELECT getdate() dte, DATEPART(dw, getdate()) dteDW, $$DATEFIRST dteFirst,
@@language LanguageVersion
</cfquery>
<cfdump expand="Yes" label="Query Dump" var="#dte0">

<cfquery name="dte0" datasource="test456"
SELECT getdate() dte, DATEPART(dw, getdate()) dteDW, $$DATEFIRST dteFirst,
@@language LanguageVersion
</cfquery>
<cfdump expand="Yes" label="Query Dump" var="#dte0">

IE: both queries to each DSN within the same CF template, executed at the
same time.

(note: you have a syntax error in your <cfdump> statement, so that actually
*isn't* the code you're running, because it would error).

If you run the query via some other client (like Query Analyser, or SQL
Studio), what do you get? Does it differ from what CF presents?

--
Adam

Well, I do not know for certain that the cause is set datefirst. Just that the behavior indicates something is changing the day of week or possibly other settings. IIRC set datefirst _should_ only apply to the current setting. But throw connection pooling into the mix and my understanding of "session" blurs a bit ;-)

Thanks so much for your quick reply. Your explanation does make a lot of sense. The data source "test123" is used by different parts of the application, so there is a bunch of CFM code using this data source. As I read your reply again, I suspect, there is some code that is doing the "SET DATEFIRST". So when somebody calls that piece of code, it sets a new value, and I happen to query this new value. That is until another piece of code calls "SET DATEFIRST" again and resets it to the correct value. This might explain the flip-flop behavior of the query.
I will ask the programmer to look at his code and check on this possibility.

On the other hand though - the other data source, "test456" that accesses the same database instance with the same ID and password, always returns the correct values. This is why I am baffled. So if it happens that something updates the value of DATEFIRST, why is "test456" returning the correct values?

Thanks again.

In the meantime if there are any other suggestions or ideas, please share.

> only the Day of week and First day of week; the timestamp is ALWAYS correct.

Unfortunately, I do not know about the datasource problem. I would have guessed that something is changing the first day of week setting. But your statement that a duplicate datasource returns the correct results, suggests otherwise. Assuming the database settings really are identical.

Having said that, what you are describing is the expected behavior. The getDate() function is non-deterministic, whereas the day of week function is deterministic, meaning the results may change depending on the value of @@DATEFIRST. So in that respect, the behavior you are describing is correct.

If you run this code in ms sql, notice the day of week changes when you change the dateFirst setting.

DECLARE @firstDayOfWeek int
SET @firstDayOfWeek = @@DATEFIRST

--- display the current value
SELECT '@@DATEFIRST = '+ cast( @firstDayOfWeek as varchar)

-- change dateFirst
SELECT datePart(dw, getdate()) DayOfWeekUsingDefault
SET DATEFIRST 3
SELECT datePart(dw, getdate()) DayOfWeekUsingDay3

--- reset back to previous value
SET DATEFIRST @firstDayOfWeek