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