DateFirst in SQL Server

[chettiar]

Hi,

I am trying to migrate SQL Server into DB2. In SQL Server there is code
like:
SET DATEFIRST 6
SELECT DATEPART(WEEK, '2006-01-31')


What could be the equivalent DB2 code for this.

Can anyone please helpe me

Chettiar

[Tonkuma]

Re: DateFirst in SQL Server

> SET DATEFIRST 6[color=blue]
> SELECT DATEPART(WEEK, '2006-01-31')[/color]
What is the result of this code?
What are meaning of "SET DATEFIRST 6" and "DATEPART(W EEK,
'2006-01-31')"?

[Kiran Nair]

Re: DateFirst in SQL Server

Did you try DAYOFWEEK scalar function ?

[Baski]

Re: DateFirst in SQL Server

Hi,

To migrate from MS-SQL to DB2, you should be clear as to what the
MS-SQL syntax/keywords/functions does before you find the equivalent
in DB2.

There is a, MS-SQL to DB2 convrsion guide available in (text and PDF
format) the IBM website in the following URL.

http://www.redbooks.ibm.com/abstracts/sg246672.html?Open

If you want to clarify things regarding the MS-SQL syntax/keywords,
then visit MSDN library in the following URL, and look for the
information under,
Servers and Enterprise development -> SQL Server -> SQL Server 2000 ->
Transact SQL reference.

http://msdn.microsoft.com/library.

Hope this info helps.

Cheers.
Baski.

[chettiar]

Re: DateFirst in SQL Server

I have tried the DAYOFWEEK function but of no use. I searched in all
the redbooks for conversion.

All I am trying to do is to make Saturday as the firstdayof the week
for my calcuation.

Is there a DB2 equivalent.


Baski wrote:[color=blue]
> Hi,
>
> To migrate from MS-SQL to DB2, you should be clear as to what the
> MS-SQL syntax/keywords/functions does before you find the equivalent
> in DB2.
>
> There is a, MS-SQL to DB2 convrsion guide available in (text and PDF
> format) the IBM website in the following URL.
> http://www.redbooks.ibm.com/abstract...6672.html?Open
>
> If you want to clarify things regarding the MS-SQL syntax/keywords,
> then visit MSDN library in the following URL, and look for the
> information under,
> Servers and Enterprise development -> SQL Server -> SQL Server 2000 ->
> Transact SQL reference.
>
> http://msdn.microsoft.com/library.
>
> Hope this info helps.
>
> Cheers.
> Baski.[/color]

[Konstantin Andreev]

Re: DateFirst in SQL Server

chettiar писал(а):
[color=blue]
> SELECT DATEPART(WEEK, '2006-01-31')[/color]

The exact equivalent is: db2 => values( week( '2006-01-31' ))
[color=blue]
> SET DATEFIRST 6[/color]

In the context where you have the day of week number, for instance, in
range 1-7, you can manually shift the "DATEFIRST" to the arbitrary day
in 2 arithmetic operations: adding appropriate constant and division by
modulus 7. You can even wrap this into UDF.
--
Konstantin Andreev.

[Dave Hughes]

Re: DateFirst in SQL Server

chettiar wrote:
[color=blue]
> I have tried the DAYOFWEEK function but of no use. I searched in all
> the redbooks for conversion.
>
> All I am trying to do is to make Saturday as the firstdayof the week
> for my calcuation.
>
> Is there a DB2 equivalent.
>
>
> Baski wrote:[color=green]
> > Hi,
> >
> > To migrate from MS-SQL to DB2, you should be clear as to what the
> > MS-SQL syntax/keywords/functions does before you find the
> > equivalent in DB2.
> >
> > There is a, MS-SQL to DB2 convrsion guide available in (text and
> > PDF format) the IBM website in the following URL.
> > http://www.redbooks.ibm.com/abstract...6672.html?Open
> >
> > If you want to clarify things regarding the MS-SQL syntax/keywords,
> > then visit MSDN library in the following URL, and look for the
> > information under,
> > Servers and Enterprise development -> SQL Server -> SQL Server
> > 2000 -> Transact SQL reference.
> >
> > http://msdn.microsoft.com/library.
> >
> > Hope this info helps.
> >
> > Cheers.
> > Baski.[/color][/color]

Well, we can make Saturday the first day of the week using a little
logic or a little math:

Checking DAYOFWEEK it returns 1-7 where 1 is Sunday. Presumably you
want to convert this to 1 for Saturday, 2 for Sunday, etc. So we need
an expression which produces the following results:

Day DAYOFWEEK Result
========= ========= ======
Sunday 1 2
Monday 2 3
Tuesday 3 4
Wednesday 4 5
Thursday 5 6
Friday 6 7
Saturday 7 1

It should be fairly obvious that we could do this with a simple CASE
expression. Assuming D is your date field:

CASE DAYOFWEEK(D) = 7 THEN 1 ELSE DAYOFWEEK(D) + 1 END

Alternatively we can produce a more flexible solution with a modulo
(division remainder) operation:

MOD(DAYOFWEEK(D ), 7) + 1

We can generalize the above expression to enable us to make *any* day
of the week the first day by adding an offset to the result of
DAYOFWEEK within the modulo function. For example, the following will
make Friday the first day of the week:

MOD(DAYOFWEEK(D ) + 1, 7) + 1

Or, to make Thursday the first day:

MOD(DAYOFWEEK(D ) + 2, 7) + 1

In other words, the general formula is:

MOD(DAYOFWEEK(D ) + N, 7) + 1

Having briefly skimmed the Transact-SQL reference, it would appear that
the equivalent in SQL Server would be:

((DATEPART(DW, D) + N) % 7) + 1

As the % operator is the modulo operator in SQL Server (and assuming
DATEFIRST is set to 7, the default).

If that calculation you're trying to convert is different from the
above, could you post a few more details about it?


HTH,

Dave.

--

[Brian Tkatch]

Re: DateFirst in SQL Server

The FUNCTION to get the week is WEEK() or WEEK_ISO(). WEEK() uses
Sunday as the beginning of the week.

Note: Jan 1 2004 was a Thursday

db2 => values week(date('01/01/2004'))

1
-----------
1

1 record(s) selected.

db2 => values week(date('01/02/2004'))

1
-----------
1

1 record(s) selected.

db2 => values week(date('01/03/2004'))

1
-----------
1

1 record(s) selected.

db2 => values week(date('01/04/2004'))

1
-----------
2

If what you want is to have the Saturday (01/03/2004) also be the next
week, a simple CASE expression adding one when it is Saturday should
do.

db2 => values week(date('01/01/2004')) + case
dayofweek(date( '01/01/2004')) when
7 then 1 else 0 end

1
-----------
1

1 record(s) selected.

db2 => values week(date('01/02/2004')) + case
dayofweek(date( '01/02/2004')) when
7 then 1 else 0 end

1
-----------
1

1 record(s) selected.

db2 => values week(date('01/03/2004')) + case
dayofweek(date( '01/03/2004')) when
7 then 1 else 0 end

1
-----------
2

1 record(s) selected.

db2 => values week(date('01/04/2004')) + case
dayofweek(date( '01/04/2004')) when
7 then 1 else 0 end

1
-----------
2

1 record(s) selected.

This can be added into a FUNCTION, even accepting anyday as the
starting day.

B.