Access Date Function that is the equivalent of to_char(Date,'w') in Oracle

Posted on 2003-03-19
Medium Priority
Last Modified: 2007-12-19
I'm trying to use the DateDiff function in Oracle, but it doesn't work the same as Oracles function.  For instance, I want March 1, 2003 - March 7, 2003 to show up as "1" when getting the 1st week of this month.  Currently March 1, 2003 is "1" and March 2, 2003 is "2".

Can anyone help?
Question by:jjcolo
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 18

Expert Comment

ID: 8168162
LVL 18

Expert Comment

ID: 8168184
this will give the number of weeks difference
(I have never used datediff to output weeks, try ww alos, let me know)
LVL 41

Expert Comment

ID: 8168209
I think you want:

However - this doesn't work if the first two dates happen to be in different weeks (as they are for March 1st and March 2nd).

What you will need to do is write your own calculation:
WeekDifference: (([Date2]-[Date1])\7)

or something similar. I don't know how the Oracle function works so it's difficult to be more explicit. Can you describe it in more detail if the above does not fulfil your requirement ?
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.


Expert Comment

ID: 8168230
Use the Format function


or the DatePart function


to get the week of the year (1-51).

Expert Comment

ID: 8168338
There is no built-in function to calculate the week of the month, but can calculate that by subtracting today's date from the first day of the month:

Function WeekOfMonth(dtIn)

Dim dtFirst As Date
Dim btWeek as Byte

dtFirst = CDate(Month(dtIn) & "/" & Year(dtIn))
btWeek = DatePart("ww", dtIn - dtFirst)

If btWeek > 51 Then
End If

End Function
LVL 18

Expert Comment

ID: 8168370
Maybe we did not understand what you are looking for.  is it possible you want to know that mar1~mar7 is week 1, mar 8~mar15, week 2...?   You should be able to sinply divide the day date by seven to get the week,if there is a remainder, add 1 'cause you're in the next week.

Accepted Solution

mcallarse earned 1000 total points
ID: 8168660
to_char(Date,'w') calculates the week of the month.

You can use division as 1William suggests, and this is probably a cleaner option. To do this, you will need to make the day negative and use the Int function to round it up, then use the Abs function to make the value positive:

=Abs(Int(-Day(dtIn) / 7))

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question