datename problems

Thanks for the reply SG,

I tried the Set Datefirst prior to running my query and i got exactly the same results (screenshot attached). I even verifird the Datefirst value immediately prior to running my query.

I suspect you are right however. Are there any other settings i should look at? would it be a service pack issue?

thanks

ozzThis mistake is at datename(dw,day(Saledate)).
You must change to
select datename(dw,Saledate) as ....

See my examples:

Datename gives incorrect result

Hi!
I tried to run this query:
select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
The result is:
34 33
It's worng result, why?
Right answer is 33 in both datenamn item.
I have SQL Server 2000
Best regards
Bertil MorefltSQL Server doesn't calculate ws according to the ISO standard. I.e., don'
t use datepart or
datename for w number calculation. Search Books Online for ISOW and us
e that one instead. Or
use a calendar table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bertil Morefalt" <bertil@.community.nospam> wrote in message
news:%23rUlP0%23pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I tried to run this query:
> select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
> datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
> The result is:
> 34 33
> It's worng result, why?
> Right answer is 33 in both datenamn item.
> I have SQL Server 2000
> Best regards
> Bertil Moreflt|||If you are looking for ISOWEEK, you can find one at the CREATE Function
example in BOL
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Bertil Morefalt" <bertil@.community.nospam> wrote in message
news:%23rUlP0%23pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I tried to run this query:
> select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
> datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
> The result is:
> 34 33
> It's worng result, why?
> Right answer is 33 in both datenamn item.
> I have SQL Server 2000
> Best regards
> Bertil Moreflt|||Here you will find a function to calculate the iso w.
http://msdn.microsoft.com/library/d...r />
_7r1l.asp
AMB
"Bertil Morefalt" wrote:

> Hi!
> I tried to run this query:
> select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
> datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
> The result is:
> 34 33
> It's worng result, why?
> Right answer is 33 in both datenamn item.
> I have SQL Server 2000
> Best regards
> Bertil Moref?lt
>|||You can use a calendar table for this, or the ISOWEEK() function in Books
Online, or the one listed here:
http://www.aspfaq.com/2519
"Bertil Morefalt" <bertil@.community.nospam> wrote in message
news:%23rUlP0%23pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I tried to run this query:
> select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
> datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
> The result is:
> 34 33
> It's worng result, why?
> Right answer is 33 in both datenamn item.
> I have SQL Server 2000
> Best regards
> Bertil Moreflt

DateName always return DatePart!

Hi all,
I have a Japanese SQL2K server that uses the following servertime format
returned by getdate() - 2005-11-23 23:29.
The problem i'm experiencing is when I use the DATENAME function, it returns
me the INT value for November (11) and not the name. Very weird. It works
correctly on my English server and i've never had this problem before using
DATENAME.
The selects are below:
SELECT DATEPART(month, GETDATE()) -- returns 11 - Correct
SELECT DATENAME(month,GETDATE()) -- returns 11 - Incorrect
Any help would be appreciated.
Regards
ImmyHi, Immy
The values returned by the DATENAME function can be in found the
syslanguages table (in the master database). Besides Japanese, this
problem also affects Korean and Chinese languages.
Razvan|||I know where I can find them , but that doesn't help the problem I am
having.
Simply, the DATENAME function should return the Name of the Day, Month
etc... but I always get the datepart int returned.
The syslanguages table on the "good" and "bad" server are exactly the same,
yet 1 server behaves correctly and the other as above!?
very !!!
Immy
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1132756544.307907.136100@.g47g2000cwa.googlegroups.com...
> Hi, Immy
> The values returned by the DATENAME function can be in found the
> syslanguages table (in the master database). Besides Japanese, this
> problem also affects Korean and Chinese languages.
> Razvan
>|||What do you want SQL Server to return ? "November" or the name of the
eleventh month in Japanese ?
If you want it to return "November", you can use one of the following
methods:
1. issue a "SET LANGUAGE ENGLISH" before executing your query
2. change the connection string to include "Current Language=English"
3. change the default language of the server, using one of the
following methods:
a) execute "EXEC sp_configure 'default language', 'English'
RECONFIGURE"
b) using Enterprise Manager, right click on your server, Properties /
Server Settings / Default language for user.
Razvan|||> 3. change the default language of the server, using one of the
> following methods:
> a) execute "EXEC sp_configure 'default language', 'English'
> RECONFIGURE"
> b) using Enterprise Manager, right click on your server, Properties /
> Server Settings / Default language for user.
Note that this setting will only affect the default language for new logins,
not for existing
logins.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1132758818.247140.278630@.g49g2000cwa.googlegroups.com...
> What do you want SQL Server to return ? "November" or the name of the
> eleventh month in Japanese ?
> If you want it to return "November", you can use one of the following
> methods:
> 1. issue a "SET LANGUAGE ENGLISH" before executing your query
> 2. change the connection string to include "Current Language=English"
> 3. change the default language of the server, using one of the
> following methods:
> a) execute "EXEC sp_configure 'default language', 'English'
> RECONFIGURE"
> b) using Enterprise Manager, right click on your server, Properties /
> Server Settings / Default language for user.
> Razvan
>|||"Immy" <imtiaz_ullah@.hotmail.com> wrote in message
news:u7HgJwD8FHA.3760@.TK2MSFTNGP14.phx.gbl...
>I know where I can find them , but that doesn't help the problem I am
>having.
> Simply, the DATENAME function should return the Name of the Day, Month
> etc... but I always get the datepart int returned.
> The syslanguages table on the "good" and "bad" server are exactly the
> same, yet 1 server behaves correctly and the other as above!?
> very !!!
> Immy
>
Hi Immy,
May I make a suggestion.
Our websites support both French and English but we have never used SQL
server to return the name of the month or w day to the client.
SQL returns the 1-12 for months and 1-7 for days and we client side
functions to display the names in the language of choice.
When you say:
"It works correctly on my English server and i've never had this problem
before using DATENAME."
Do you mean it returns the correct English name or Japanese?
If you mean English and that server does not return the correct Japanese
name, then I suggest that you look at the sysLanguage table as Razvan has
suggested. You'll see that for Japanese, the language names are listed as
1,2,3,4,5... instead of the actual names.
I don't know the reason why.|||Tibor Karaszi wrote:
> Note that this setting will only affect the default language for new login
s,
> not for existing logins.
You are correct. I should have mentioned another method:
4. change the default language of the login used by your application,
using one of the following methods:
a) execute: EXEC sp_defaultlanguage 'YourLoginName', 'English'
b) using Enterprise Manager, go to "Security" / "Logins", double click
on your login, and change the "Language" setting under "General" /
"Defaults".
Razvan

DATENAME - can you not get it to say the actual day name?

SELECT DATENAME(day, getdate()) AS 'Day Name'

comes back as 15 but i want it to come back with thursday

Cheers,
Craig

use dw

SELECT DATENAME(dw, getdate()) AS 'Day Name'

this stuff is clearly documented in BOL with in the section on datename

|||

what makes it worse is that i was sitting looking at datename page in BOL

jsut one of those days where you cant see the wood for the trees!!

soz,
Craig

|||at least you looked before you posted :)