DateTime Bugs?

Hi,
Below shown simple script to get the wday. Any idea why the wday
for spanish datetime is 1 instead of 2 for 'Ene 16 2006 2:00PM' ('Jan 16
2006 2:00PM') '
TEST
--
print DATEPART(dw,'Jan 16 2006 2:00PM')
SET LANGUAGE spanish
print getdate()
declare @.datetime datetime
set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
print @.datetime
print DATEPART(dw,@.datetime)
print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
SET LANGUAGE us_english
OUTPUT
--
2
Changed language setting to Espaol.
Ene 19 2006 9:58PM
Ene 16 2006 2:00PM
1
1
Changed language setting to us_english.
Thanks,
KennyI believe it is something to do with which day of the w to be considered
as first day. As default (English) it is Sunday.
If you issue SET DATEFIRST 7 (7 represents Sunday) just before DATAEPART
function, it should solve your "bug".
print DATEPART(dw,'Jan 16 2006 2:00PM')
SET LANGUAGE spanish
print getdate()
declare @.datetime datetime
set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
print @.datetime
SET DATEFIRST 7
print DATEPART(dw,@.datetime)
print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
SET LANGUAGE us_english
"Kenny" <keejh@.hotmail.com> wrote in message
news:%23XFnc6WHGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Below shown simple script to get the wday. Any idea why the wday
> for spanish datetime is 1 instead of 2 for 'Ene 16 2006 2:00PM' ('Jan 16
> 2006 2:00PM') '
> TEST
> --
> print DATEPART(dw,'Jan 16 2006 2:00PM')
> SET LANGUAGE spanish
> print getdate()
> declare @.datetime datetime
> set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
> print @.datetime
> print DATEPART(dw,@.datetime)
> print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
> SET LANGUAGE us_english
> OUTPUT
> --
> 2
> Changed language setting to Espaol.
> Ene 19 2006 9:58PM
> Ene 16 2006 2:00PM
> 1
> 1
> Changed language setting to us_english.
> Thanks,
> Kenny
>|||Microsoft failed to follow ISO standards about day of the wek numbers.
They also wrote their own version of ws-within-year numbers.|||What are you talking about? 8601 was not even out until 1988 and was not
popular until second version in 2000. Sybase was created before that.
Also, check the calendar on your desk. It starts with Sunday. People were
using start of w on Sunday long before ISO. Besides, you can change the
start day anyway.
William Stacey [MVP]
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137739565.063520.234520@.g14g2000cwa.googlegroups.com...
| Microsoft failed to follow ISO standards about day of the wek numbers.
| They also wrote their own version of ws-within-year numbers.
||||Hello, Joe
Indeed, the w numbers returned by the DATEPART are not the ISO w
numbers. There is an example in Books Online on how to create a UDF to
return the ISO w number. However, the original poster was talking
about wdays, not w numbers (which is a completely different
thing).
Razvan|||As indicated in other posts, day of w is dependent on which country you l
ive in. In the US,
Sunday is the first day of the w. In Sweden (and majority of Europe, prob
ably all), first day of
w is Monday. DATEPART to calculate day of w is dependent on SET LANGUA
GE and can be overridden
with SET DATEFIRST.
set language us_english
print DATEPART(dw,getdate())
set language british
print DATEPART(dw,getdate())
set language spanish
print DATEPART(dw,getdate())
set language polish
print DATEPART(dw,getdate())
set language german
print DATEPART(dw,getdate())
set language swedish
print DATEPART(dw,getdate())
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kenny" <keejh@.hotmail.com> wrote in message news:%23XFnc6WHGHA.3936@.TK2MSFTNGP12.phx.gbl..
.
> Hi,
> Below shown simple script to get the wday. Any idea why the wday
for spanish datetime is
> 1 instead of 2 for 'Ene 16 2006 2:00PM' ('Jan 16 2006 2:00PM') '
> TEST
> --
> print DATEPART(dw,'Jan 16 2006 2:00PM')
> SET LANGUAGE spanish
> print getdate()
> declare @.datetime datetime
> set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
> print @.datetime
> print DATEPART(dw,@.datetime)
> print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
> SET LANGUAGE us_english
> OUTPUT
> --
> 2
> Changed language setting to Espaol.
> Ene 19 2006 9:58PM
> Ene 16 2006 2:00PM
> 1
> 1
> Changed language setting to us_english.
> Thanks,
> Kenny
>