DATEPART ( Caldayofweek returns 1 for Monday, not Sunday

[Breck Carter [TeamSybase]]

Either the behavior is correct (it *should* be different from Weekday)
or the Help is correct. Some clarification is requested :)

Breck

ASA SQL Reference
SQL Functions
Function types
Date and time functions
Date parts

...

Caldayofweek cdw 1–7. (Sunday = 1, ..., Saturday = 7)

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
***@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

[Nick Elson]

Hey Breck,

I believe you have stepped into ISO standard territory here ... and you
may actually be seeing normal behaviour here.

Let me recap what I do know first:

Another reference for DatePart is the ASE Ref. Man. Part 1
and there you will find a lot more Comments about how this
is all supposed to work.

http://download.sybase.com/pdfdocs/asg1250e/refman1.pdf
or
http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/refman

and look for datepart( )

When you look there in the comments you will note that the part
about these Date Parts

cal*

have slghtly different meanings from what you may be expecting.

More to come on this point . . .maybe ... given time .... but feel free to
read up on this . . .

But here are some relevant quotes

"datepart returns a number that follows ISO standard 8601, which
defines
the first day of the week and the first week of the year. Depending
on
whether the datepart function includes a value for calweekofyear,
calyearofweek, or caldayorweek, the date returned may be different
for the
same unit of time."

"The date part weekday or dw returns the corresponding number when
used
with datepart. The numbers that correspond to the names of weekdays
depend on the datefirst setting.. . . "

Post by Breck Carter [TeamSybase]
Either the behavior is correct (it *should* be different from Weekday)
or the Help is correct. Some clarification is requested :)
Breck
ASA SQL Reference
SQL Functions
Function types
Date and time functions
Date parts
...
Caldayofweek cdw 1-7. (Sunday = 1, ..., Saturday = 7)
--
SQL Anywhere Studio 9 Developer's Guide
http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

[Nick Elson]

Yep T/SQL standard here.

If I run

select datepart( caldayofweek, '2005-01-01' ), datepart( dw,
'2005-01-01' )

on both ASA 9.0.2 and ASE 12.5 I get

datepart(caldayofweek,'2005-01-01') datepart(dw,'2005-01-01')
6 7
or
----------- -----------
6 7
(1 row affected)

in both cases [w\default N.Am. settings]

Setting DATEFIRST as

set datefirst 1
go

then the result set turns into 6,6 on both platforms.


[Also see: the ASA option FIRST_DAY_OF_WEEK option]

Post by Nick Elson
Hey Breck,
I believe you have stepped into ISO standard territory here ... and you
may actually be seeing normal behaviour here.
Another reference for DatePart is the ASE Ref. Man. Part 1
and there you will find a lot more Comments about how this
is all supposed to work.
http://download.sybase.com/pdfdocs/asg1250e/refman1.pdf
or
http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/refman
and look for datepart( )
When you look there in the comments you will note that the part
about these Date Parts
cal*
have slghtly different meanings from what you may be expecting.
More to come on this point . . .maybe ... given time .... but feel free to
read up on this . . .
But here are some relevant quotes
"datepart returns a number that follows ISO standard 8601, which
defines
the first day of the week and the first week of the year.
Depending on
whether the datepart function includes a value for calweekofyear,
calyearofweek, or caldayorweek, the date returned may be different
for the
same unit of time."
"The date part weekday or dw returns the corresponding number when
used
with datepart. The numbers that correspond to the names of weekdays
depend on the datefirst setting.. . . "

Post by Breck Carter [TeamSybase]
Either the behavior is correct (it *should* be different from Weekday)
or the Help is correct. Some clarification is requested :)
Breck
ASA SQL Reference
SQL Functions
Function types
Date and time functions
Date parts
...
Caldayofweek cdw 1-7. (Sunday = 1, ..., Saturday = 7)
--
SQL Anywhere Studio 9 Developer's Guide
http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

...

[Breck Carter [TeamSybase]]

So the Help needs changing?

Breck

On 25 Feb 2005 08:38:11 -0800, "Nick Elson"

Post by Nick Elson
Hey Breck,
I believe you have stepped into ISO standard territory here ... and you
may actually be seeing normal behaviour here.
Another reference for DatePart is the ASE Ref. Man. Part 1
and there you will find a lot more Comments about how this
is all supposed to work.
http://download.sybase.com/pdfdocs/asg1250e/refman1.pdf
or
http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/refman
and look for datepart( )
When you look there in the comments you will note that the part
about these Date Parts
cal*
have slghtly different meanings from what you may be expecting.
More to come on this point . . .maybe ... given time .... but feel free to
read up on this . . .
But here are some relevant quotes
"datepart returns a number that follows ISO standard 8601, which
defines
the first day of the week and the first week of the year. Depending
on
whether the datepart function includes a value for calweekofyear,
calyearofweek, or caldayorweek, the date returned may be different
for the
same unit of time."
"The date part weekday or dw returns the corresponding number when
used
with datepart. The numbers that correspond to the names of weekdays
depend on the datefirst setting.. . . "

Post by Breck Carter [TeamSybase]
Either the behavior is correct (it *should* be different from Weekday)
or the Help is correct. Some clarification is requested :)
Breck
ASA SQL Reference
SQL Functions
Function types
Date and time functions
Date parts
...
Caldayofweek cdw 1-7. (Sunday = 1, ..., Saturday = 7)
--
SQL Anywhere Studio 9 Developer's Guide
http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

...

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
***@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

[Nick Elson]

I believe so ... [note my other posting as well] ...

In fact it is documented but in the change of behaviour notice
for 7.0.2


What's New in SQL Anywhere Studio
What's New in Version 7.0.2
New features in version 7.0.2
Adaptive Server Anywhere new features

"First day of week option"

which documents the behaviour of "weekday" (not using
highlighted text though) but it is moot on "dw" (if you just
happen to miss the comment about "weekday").

Obviously this text needs to be reworked and added
into the Date Parts section.

I will follow up.

Post by Breck Carter [TeamSybase]
So the Help needs changing?
Breck
On 25 Feb 2005 08:38:11 -0800, "Nick Elson"

Post by Nick Elson
Hey Breck,
I believe you have stepped into ISO standard territory here ... and you
may actually be seeing normal behaviour here.
Another reference for DatePart is the ASE Ref. Man. Part 1
and there you will find a lot more Comments about how this
is all supposed to work.
http://download.sybase.com/pdfdocs/asg1250e/refman1.pdf
or
http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/refman
and look for datepart( )
When you look there in the comments you will note that the part
about these Date Parts
cal*
have slghtly different meanings from what you may be expecting.
More to come on this point . . .maybe ... given time .... but feel free to
read up on this . . .
But here are some relevant quotes
"datepart returns a number that follows ISO standard 8601, which
defines
the first day of the week and the first week of the year. Depending
on
whether the datepart function includes a value for calweekofyear,
calyearofweek, or caldayorweek, the date returned may be different
for the
same unit of time."
"The date part weekday or dw returns the corresponding number when
used
with datepart. The numbers that correspond to the names of weekdays
depend on the datefirst setting.. . . "

Post by Breck Carter [TeamSybase]
Either the behavior is correct (it *should* be different from Weekday)
or the Help is correct. Some clarification is requested :)
Breck
ASA SQL Reference
SQL Functions
Function types
Date and time functions
Date parts
...
Caldayofweek cdw 1-7. (Sunday = 1, ..., Saturday = 7)
--
SQL Anywhere Studio 9 Developer's Guide
http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

--
SQL Anywhere Studio 9 Developer's Guide
http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

...