Week Number from Date

[pooja821]

Hi,

I want to fetch week number from date(date format is DD-MMM-YYYY).

For Eg My date is 02-Oct -2017. Since this date lies in week 2 so i  want the output to be 2.

Given condition is that my week start from Monday.

For e.g. on 01-Oct-2017, its Sunday so it would come under week 1 and on from Monday on wards my week number would 2 for that specific week.

Kindly help.

[Gysbert_Wassenaar]

Personally I wouldn't bother trying and instead create the calendar in an excel file. Then load the dates and the weeks from excel file.

[pooja821]

How would we able to do that.

Its just i want to fetch Week number from date.

I have done it using expression :Ceil((Day(date(date#(Date,'DD-MMM-YYYY'),'DD')))/7) as WeekNumber in load editor, but now the problem i m facing is that i want the first day of week to start with Monday as i explained in my scenario.

[olivierrobin]

hello

why don't you use the parameters ok week function?  (see documentation)

week(timestamp [, first_week_day [, broken_weeks [, reference_day]]])

Return data type: integer

[pooja821]

can you pls elaborate a little?

[olivierrobin]

say you column is named myDate

if you use the function week(MyDate,0), il will compute the week with monday as first day

have a look at the documentation on this function to see various parameters

[pooja821]

it basically giving the number of weeks before that specified date.

Still not helping.

[olivierrobin]

it returns the week number of the date in the year

[pooja821]

Exactly..

it returns the week number of the date in the year but i want week of the date in the month.

[olivierrobin]

try to calculate the week number of your date minus og the 1st day of the month