Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3684
  • Last Modified:

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

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?
0
jjcolo
Asked:
jjcolo
  • 3
  • 3
1 Solution
 
1WilliamCommented:
datediff("w",date1,date2)
0
 
1WilliamCommented:
or....
format(Date2,"ww")-format(Date1,"ww")
this will give the number of weeks difference
(I have never used datediff to output weeks, try ww alos, let me know)
0
 
shanesuebsahakarnCommented:
I think you want:
DateDiff("ww",[DateField1],[DateField2])

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 ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mcallarseCommented:
Use the Format function

Format(myDate,"ww")

or the DatePart function

DatePart("ww",myDate)

to get the week of the year (1-51).
0
 
mcallarseCommented:
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
  WeekOfMonth=1
Else
  WeekOfMonth=btWeek
End If

End Function
0
 
1WilliamCommented:
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.
0
 
mcallarseCommented:
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))
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now