ISO Year/week numbers - like ORACLE IYYYY/IW

[jonhowe]

I am just converting a system from ORACLE to SQLServer 2000.

We have a lot of sql statements in ORACLE syntax that use the to_char(date,IYYY) and to_char(date,IW) to bring back the ISO year and week number.

In ORACLE, using to_char, IYYY & IW do give different results to YYYY and WW around a calendar year end/start.

In SQLServer do functions such as
datepart(yy,date)
datename(YY,date)
YEAR(date)
bring back an ISO year ?

 

[SQLBill]

What is ISO? Can you give us examples?

-SQLBill

 

[jonhowe]

A good illustration is for the date 29-DEC-2003 :

in ORACLE :

select to_char(to_date('12/29/2003','mm/dd/yyyy'),'YYYY') from dual;

result : 2003

select to_char(to_date('12/29/2003','mm/dd/yyyy'),'IYYY') from dual;

result : 2004

select to_char(to_date('12/29/2003','mm/dd/yyyy'),'WW') from dual;

result : 52

select to_char(to_date('12/29/2003','mm/dd/yyyy'),'IW') from dual;

result : 01

in SQL Server :

select year(convert(datetime,'29/12/2003',103))

result : 2003

select datepart(ww,(convert(datetime,'29/12/2003',103)))

result : 53

Jonathan