DateTime Bugs??

20 Jan 2006 3:07 AM

Kenny

Hi,
Below shown simple script to get the weekday. Any idea why the weekday
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 Español.
Ene 19 2006 9:58PM
Ene 16 2006 2:00PM
1
1
Changed language setting to us_english.

Thanks,
Kenny

20 Jan 2006 3:25 AM

Jonathan Chong

I believe it is something to do with which day of the week 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

Show quote

"Kenny" <ke***@hotmail.com> wrote in message
news:%23XFnc6WHGHA.3936@TK2MSFTNGP12.phx.gbl...
> Hi,
> Below shown simple script to get the weekday. Any idea why the weekday
> 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 Español.
> Ene 19 2006 9:58PM
> Ene 16 2006 2:00PM
> 1
> 1
> Changed language setting to us_english.
>
> Thanks,
> Kenny
>
>

20 Jan 2006 6:46 AM

--CELKO--

Microsoft failed to follow ISO standards about day of the wek numbers.
They also wrote their own version of weeks-within-year numbers.

20 Jan 2006 7:14 AM

William Stacey [MVP]

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 week on Sunday long before ISO. Besides, you can change the
start day anyway.

--
William Stacey [MVP]

Show quote

"--CELKO--" <jcelko***@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 weeks-within-year numbers.
|

20 Jan 2006 8:18 AM

Razvan Socol

Hello, Joe

Indeed, the week numbers returned by the DATEPART are not the ISO week
numbers. There is an example in Books Online on how to create a UDF to
return the ISO week number. However, the original poster was talking
about weekdays, not week numbers (which is a completely different
thing).

Razvan

20 Jan 2006 8:32 AM

Tibor Karaszi

As indicated in other posts, day of week is dependent on which country you live in. In the US,
Sunday is the first day of the week. In Sweden (and majority of Europe, probably all), first day of
week is Monday. DATEPART to calculate day of week is dependent on SET LANGUAGE 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/

Show quote

"Kenny" <ke***@hotmail.com> wrote in message news:%23XFnc6WHGHA.3936@TK2MSFTNGP12.phx.gbl...
> Hi,
> Below shown simple script to get the weekday. Any idea why the weekday 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 Español.
> Ene 19 2006 9:58PM
> Ene 16 2006 2:00PM
> 1
> 1
> Changed language setting to us_english.
>
> Thanks,
> Kenny
>