How can I find the week number of the year?

[bradlee27514]

I can't seem to figure this out with the available date functions. I want to take a date (e.g. currentdate) and have Crystal return what week of the year (e.g. 43) it is. Any ideas?

 

[fisheromacse]

Depending on what your endgame is, i think if you use one of the below it will get the value you are desiring:

As a SQL Expression:
DATEPART(wk,"tablename"."fieldname")

or as a formula:
DATEPART('ww',tablename.fieldname)

 

[MCuthill]

Another Option:

DateDiff ("w",Date(2010,01,01),{Table.Field})

"Date(2010,01,01)" can be substituted with a formula that contains the first of the current year, otherwise would need changed every year.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[bradlee27514]

thank you so much guys!

 

[fisheromacse]

I don't know why you would want to use datediff. It would give you the number of weeks between the dates, but would that necessarily be equal to the datepart in all instances? i have never tested this, so can't say for sure...
but if you want to use datediff and have it always use the current year, you could do so like this:

DateDiff ("w",Date(year(currentdate),01,01),{Table.Field})

 

[MCuthill]

Hey Fisher,

I didn't test it extensively, but it seemed to be valid for my test dates. The benefit of DateDiff (that I can think of) is if you are not working on Calendar Week (ie your fiscal year starts October 1st every year), then you could get the Fiscal Week of the organization using the same approach.



Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

*lightbulb* One observation though... DateDiff will return the number of FULL weeks.

Jan 1, 2010 to Jan 18th, 2010 would return 2, though it is arguably the 3rd week, guess it depends on your end need.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

datepart() allows you to also specify arguments for the first week of year and for first day of the week--explained in the Help section. I think the convention is to use "ww", also.

-LB