CODEWITHSUNDEEP
sqloracle-database
e.ozmen
e.ozmen

Reputation: 253

Query to get people born this week

I am trying to get the records where the birthday falls within this week but my query returns the people who were born between Tuesday and Monday. I want the people born between Monday and Sunday. How can I change my query?

WHERE TO_CHAR(SYSDATE, 'WW') = TO_CHAR(BIRTHDAY_DATE, 'WW')

Upvotes: 3

Views: 705

Answers (3)

elrado
elrado

Reputation: 5272

Probably Monday is not first day of your week.

Under system you can get NLS_TERRITORY value with:

show parameters NLS_TERRITORY

Under any session you can set it like that

Set it for current session.

ALTER SESSION SET NLS_TERRITORY=slovenia
SELECT to_char(to_date('13.01.2013','DD.MM.YYYY'),'d') from dual;--SUNDAY

Result is 7.

ALTER SESSION SET NLS_TERRITORY=america
select to_char(to_date('13.01.2013','DD.MM.YYYY'),'d') from dual--SUNDAY

Result is 1.

Upvotes: 2

e.ozmen
e.ozmen

Reputation: 253

Thank you who respones my question. I found the solution. Here I create a string of the day,month in current week:

DateTime sdate = DateTime.Now.AddDays(-1 * (int)DateTime.Now.DayOfWeek + 1);
        string findDay = "";
        for (int i = 0; i < 7; i++)
            findDay += (findDay.Length > 0 ? "," : "") + "'" + sdate.AddDays(i).Day.ToString("00") + "." + sdate.AddDays(i).Month.ToString("00") + "'";

And the sql:

WHERE TO_CHAR(BIRTHDAY_DATE, 'DD.MM') IN (" + findDay + ")

The 'WW' works like elrado said.

Upvotes: 1

Orangecrush
Orangecrush

Reputation: 1990

Please try this,

WHERE BIRTHDAY_DATE between sysdate - (TO_CHAR(SYSDATE, 'WW')) + 1 AND sysdate - (TO_CHAR(SYSDATE, 'WW')) + 7

Upvotes: 0

Related Questions