By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,589 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

DateFirst in SQL Server

P: n/a
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

Apr 27 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
> SET DATEFIRST 6
SELECT DATEPART(WEEK, '2006-01-31')

What is the result of this code?
What are meaning of "SET DATEFIRST 6" and "DATEPART(WEEK,
'2006-01-31')"?

Apr 27 '06 #2

P: n/a
Did you try DAYOFWEEK scalar function ?

Apr 27 '06 #3

P: n/a
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.

Apr 27 '06 #4

P: n/a
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:
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.


May 16 '06 #5

P: n/a
chettiar писал(а):
SELECT DATEPART(WEEK, '2006-01-31')
The exact equivalent is: db2 => values( week( '2006-01-31' ))
SET DATEFIRST 6


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.

May 16 '06 #6

P: n/a
chettiar wrote:
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:
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.


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.

--

May 16 '06 #7

P: n/a
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.

May 16 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.