Equivalent of MSSQL function DateFirst in MYSQL.

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.

On Mar 5, 8:56 am, Hetal <hetal.a.kapa...@gmail.com> wrote:
> 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.

typo here... sets = lets sorry!

Hetal schreef:
> 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.

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

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.

On Mar 5, 10:46 am, Luuk <L...@invalid.lan> wrote:
> Hetal schreef:
>
> > 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.
>
> 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 schreef:
> 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.
>
> On Mar 5, 10:46 am, Luuk <L...@invalid.lan> wrote:
>> Hetal schreef:
>>
>>> 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.
>> 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
>

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/...functions.html


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


--
Luuk

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

> 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.

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

Luuk,

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

Hetal.

On Mar 5, 12:40 pm, Luuk <L...@invalid.lan> wrote:
> Hetal schreef:
>
>
>
> > 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.
>
> > On Mar 5, 10:46 am, Luuk <L...@invalid.lan> wrote:
> >> Hetal schreef:
>
> >>> 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.
> >> 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
>
> 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/...functions.html
>
> mysql> Select DATEPART(WeekDay, GetDate())
> -> ;
> ERROR 1054 (42S22): Unknown column 'WeekDay' in 'field list'
> mysql>
>
> --
> Luuk

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>
> wrote:
>
> > 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.
>
> 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 wrote:
> On Mar 5, 12:40 pm, Luuk <L...@invalid.lan> wrote:
>> Hetal schreef:
>>
>>
>>
>>> 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.
>>> On Mar 5, 10:46 am, Luuk <L...@invalid.lan> wrote:
>>>> Hetal schreef:
>>>>> 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.
>>>> 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
>> 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/...functions.html
>>
>> mysql> Select DATEPART(WeekDay, GetDate())
>> -> ;
>> ERROR 1054 (42S22): Unknown column 'WeekDay' in 'field list'
>> mysql>
>>
>> --
>> Luuk
>
>
> Luuk,
>
> This query i provided is intended to run on MSSQL and not MySQL.
> Anyways, thanks for your inputs.
>
> Hetal.
>

(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.
jstucklex@attglobal.net
==================

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.


On Mar 5, 8:22 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Hetal wrote:
> > On Mar 5, 12:40 pm, Luuk <L...@invalid.lan> wrote:
> >> Hetal schreef:
>
> >>> 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.
> >>> On Mar 5, 10:46 am, Luuk <L...@invalid.lan> wrote:
> >>>> Hetal schreef:
> >>>>> 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.
> >>>> 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
> >> 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/...functions.html
>
> >> mysql> Select DATEPART(WeekDay, GetDate())
> >> -> ;
> >> ERROR 1054 (42S22): Unknown column 'WeekDay' in 'field list'
> >> mysql>
>
> >> --
> >> Luuk
>
> > Luuk,
> >
> > This query i provided is intended to run on MSSQL and not MySQL.
> > Anyways, thanks for your inputs.
> >
> > Hetal.
> >
>
> (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.
> jstuck...@attglobal.net
> ==================