Equivalent of MSSQL function DateFirst in MYSQL.

[Hetal]

Hi..

There is a function in MSSQL that sets you set what the first day of
the week should be. For e.g. if i say Set DateFirst = 1, the first day
of the week is set to Monday. If DateFirst = 2, then first day of the
week is set to Tuesday.

I would like to do something similar in MySQL but i am unable to find
any equivalent function in MySQL. Your help will be very much
appreciated.

Thanks,
Hetal.

[Hetal]

typo here... sets = lets sorry!

[Luuk]

Hetal schreef:

I do think that there's no such function,
as, in my opninion, such a function should not even exist in MSSQL,
because the first day of the week had nothing to do with databases,
its an agreement between some people stating on which day the week starts

see i.e.: http://en.wikipedia.org/wiki/ISO_week_date
A date is specified by the ISO year in the format YYYY, a week number in
the format ww prefixed by the letter W, and the weekday number, a digit
d from 1 through 7, beginning with Monday and ending with Sunday. For
example, 2006-W52-7 (or in compact form 2006W527) is the Sunday of the
52nd week of 2006. In the Gregorian system this day is called 31
December 2006.

--
Luuk

[Hetal]

Hi Luuk.

Can you please try running this query and check what the output is.
Just to brief up, it will return the day of week.

Select DATEPART(WeekDay, GetDate())

And then try running this query and see what the day of week shows up
as .

Set DateFirst 2
Select DATEPART(WeekDay, GetDate())

The output of first query will differ from the output of second query.
So my question here is, do we have something similar in MySQL that
will let us have the functionality similar to "Set DateFirst 2"
statement?

Thanks,
Hetal.

[Luuk]

Hetal schreef:

i do understand your question, but i find it dumb to have this option
available in a database.

because i believe that the first day of the week has nothing to do with
a database, so any statement like 'Set DateFirst 2' is useless.

and you can read all about the week functions available in MySQL at:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

mysql> Select DATEPART(WeekDay, GetDate())
-> ;
ERROR 1054 (42S22): Unknown column 'WeekDay' in 'field list'
mysql>

--
Luuk

[Rik Wasmus]

On Wed, 05 Mar 2008 17:56:50 +0100, Hetal <hetal.a...@gmail.com>
wrote:

SET @my_day_offset := 5;
SELECT (DAYOFWEEK(NOW()) + @my_day_offset) % 7;
SELECT (DAYOFWEEK('2008-03-03') + @my_day_offset) % 7;

Etc... define you own custom function for this if you want it, however, it
seems a very weird requitement to me. Why do you actually need this?
--
Rik Wasmus

[Hetal]

Luuk,

This query i provided is intended to run on MSSQL and not MySQL.
Anyways, thanks for your inputs.

Hetal.

[Hetal]

My requirement is simple. I need to write a query that fetches sales
total based on weekday from different databases (MSSQL, MySQL etc) and
for a specified date range. So if i run this query for a week or even
for a month, it should return 7 rows like this where 1 = Monday, 2 =
Tuesday etc..

WeekDay Sales
==============
1 15000
2 18000
3 14000
4 13000
5 19000
6 22000
7 25000

When i use function DatePart() on MSSQL with first parameter "WeekDay"
and second parameter as a date pertaining to Monday, it would return
me 2 as a result.
e.g. Select DatePart(WeekDay, '3/3/2008') result: 2

When i use function WeekDay() on MySQL with passing a date pertaining
to Monday it will return me a number 0.
e.g. Select WeekDay('2008-03-03') result: 0

I would like my query to return result as "1" for date pertaining to
monday irrespective of what the database is. I was able to make MSSQL
return 1 for date pertaining to Monday by running this statement
before the actual query.
SET DATEFIRST 1
Select DatePart(WeekDay, '3/3/2008')

Now i would like MySQL to return result as 1 for a date pertaining to
Monday. How can i achieve it?

Hi Rik,

Thanks for the suggestion.. i will try that out.

Thanks,
Hetal.

On Mar 5, 12:49 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Wed, 05 Mar 2008 17:56:50 +0100, Hetal <hetal.a.kapa...@gmail.com>

[Jerry Stuckle]

(Top posting fixed)

I agree with Luuk. A database's job is to store and retrieve data. The
day of the week should be immaterial to a database. If you want to
change it, use a programming language such as PHP.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================

[Hetal]

Thanks for your response on this post.. If someone is aware of.. can
you please explain what the below statement in MSSQL does and why does
it change the day of week, if day of week is immaterial to a database?

SET DATEFIRST 1

Also, can someone help me retrieve result as 1 for the below select
statement? (2008-03-03 is a Monday)

Select WeekDay('2008-03-03')

Thanks,
Hetal.

> jstuck...@attglobal.net
> ==================

[Captain Paralytic]

On 6 Mar, 16:40, Hetal <hetal.a.kapa...@gmail.com> wrote:
> Thanks for your response on this post.. If someone is aware of.. can
> you please explain what the below statement in MSSQL does and why does
> it change the day of week, if day of week is immaterial to a database?
>
> SET DATEFIRST 1
>
> Also, can someone help me retrieve result as 1 for the below select

If you stop top posting we might.

Anyway, what was wrong with Rik's suggestion?

[Jerry Stuckle]

Hetal wrote:
> On Mar 5, 8:22 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:

> Thanks for your response on this post.. If someone is aware of.. can
> you please explain what the below statement in MSSQL does and why does
> it change the day of week, if day of week is immaterial to a database?
>
> SET DATEFIRST 1
>

> Thanks,
> Hetal.
>
>

(Top posting fixed)

Because Microsoft is good at screwing things up? If it were important
to a database, the ANSI SQL standard would have something in about it.
But the Microsoft was never good at adhering to standards.

No other database has it, AFAIK.

P.S. Please don't top post. Thanks.

[Gordon Burditt]

>I agree with Luuk. A database's job is to store and retrieve data. The
>day of the week should be immaterial to a database. If you want to
>change it, use a programming language such as PHP.

Databases also do a lot of manipulation of data. That can include
a lot of calendar-related stuff because it's common to want stuff
like sales grouped by day of the week or deal with prices that
change based on the day of the week (telephone calls, anyone?).

There are a number of functions in SQL which deal with "week numbers",
(week(), weekofyear(), yearweek(), and the strftime() formats %U,
%u, %V, %v, %X, and %x) which are apparently used in queries like:
SELECT sum(hours) from timeclock_log_2008 where empno = 123 group
by weekofyear(starttime);
where some people are apparently using weekofyear() to divide up
the year into pay periods.

There are apparently differing standards for numbering weeks, as
evidenced by the second argument of the week() function. It matters
for these functions which day is considered to start a week.

Do all of these also not belong in a database? I admit I don't have
much use for "week numbers". Payroll and corporate finance departments
may disagree. But a lot of time manipulation, such as grouping by
months or weeks, is useful.

I *do* have a use for knowing the day of week of a date of something.
I've used that a lot of graphing traffic statistics for things like
email or network packets, which vary quite a bit within a week. It
is interesting that MySQL has two functions to detemine the day of
week for a date, dayofweek() and weekday(), which use a different
numbering system. This seems strange, especially if other databases
use even more different numbering systems for the day of the week.

I don't have all that much use for *changing* the beginning of a
week. Tell me what numbering system is in use, preferably one that
works on all implementations of SQL, and I'll use it. If
sql_standard_day_of_week() has 1 = Friday, and I want 0 = Sunday,
1 = Monday, ..., 5 = Friday, 6 = Saturday, then I'll use
(4+sql_standard_day_of_week())%7 .

[Gordon Burditt]

>Thanks for your response on this post.. If someone is aware of.. can
>you please explain what the below statement in MSSQL does and why does
>it change the day of week, if day of week is immaterial to a database?

Please don't top post.

>SET DATEFIRST 1

I'm not sure even God can change things so '2008-03-03' is a Friday,
and He probably even has a MSSQL license. What does it mean to
alter the day of the week for a specific date? Is this an attempt
to screw up Texas Early Voting laws or get back Florida and Michigan
delegates in the Democratic primary?

>Also, can someone help me retrieve result as 1 for the below select
>statement? (2008-03-03 is a Monday)
>
>Select WeekDay('2008-03-03')

SELECT (1+WEEKDAY('2008-03-03'))%7;

Note I could have cheated and used:
SELECT 1;
since you didn't specify what it was supposed to do with any other date.

[Jerry Stuckle]

Gordon Burditt wrote:
>> I agree with Luuk. A database's job is to store and retrieve data. The
>> day of the week should be immaterial to a database. If you want to
>> change it, use a programming language such as PHP.
>
> Databases also do a lot of manipulation of data. That can include
> a lot of calendar-related stuff because it's common to want stuff
> like sales grouped by day of the week or deal with prices that
> change based on the day of the week (telephone calls, anyone?).
>
> There are a number of functions in SQL which deal with "week numbers",
> (week(), weekofyear(), yearweek(), and the strftime() formats %U,
> %u, %V, %v, %X, and %x) which are apparently used in queries like:
> SELECT sum(hours) from timeclock_log_2008 where empno = 123 group
> by weekofyear(starttime);
> where some people are apparently using weekofyear() to divide up
> the year into pay periods.
>
> There are apparently differing standards for numbering weeks, as
> evidenced by the second argument of the week() function. It matters
> for these functions which day is considered to start a week.
>
> Do all of these also not belong in a database? I admit I don't have
> much use for "week numbers". Payroll and corporate finance departments
> may disagree. But a lot of time manipulation, such as grouping by
> months or weeks, is useful.
>

I didn't say that. They are all interpretations of the current data.
But changing the starting day of the week is not - that's changing the
meaning of the data, and is best left to the application, if needed.

> I *do* have a use for knowing the day of week of a date of something.
> I've used that a lot of graphing traffic statistics for things like
> email or network packets, which vary quite a bit within a week. It
> is interesting that MySQL has two functions to detemine the day of
> week for a date, dayofweek() and weekday(), which use a different
> numbering system. This seems strange, especially if other databases
> use even more different numbering systems for the day of the week.
>

IIRC, the SQL standard defines the day of the week with Saturday=0,
Sunday=1, etc. But I don't have a copy of the standard to look at right
now.

So if there is, MS's implementation is not an extension to the standard,
it is a violation of the standard. But that also wouldn't surprise me.

> I don't have all that much use for *changing* the beginning of a
> week. Tell me what numbering system is in use, preferably one that
> works on all implementations of SQL, and I'll use it. If
> sql_standard_day_of_week() has 1 = Friday, and I want 0 = Sunday,
> 1 = Monday, ..., 5 = Friday, 6 = Saturday, then I'll use
> (4+sql_standard_day_of_week())%7 .
>
>
>