Hi All,
Does anyone have a function/s for finding the week number in the year and week in the month from a given date?
i.e. 1st of Jan 2003 was a wednsday which means that 1st - 4th of Jan 2003 was the first week in the year, etc...
thanks in advance,
Roy R.
Finding week in year and week in month
Moderators: chulett, rschirm, roy
Roy
You can get it by dividing the julian day by 7.
JulianDay = oconv(MyDate, "DJ")
Week = int(JulianDay/7)
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
You can get it by dividing the julian day by 7.
JulianDay = oconv(MyDate, "DJ")
Week = int(JulianDay/7)
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Thanks Guys, BUT ,
if the 1st of Jan was a wednsday then only wed-sat are in the first week of the year, the next sunday which is the 5th day in the year is already on the second week in the year.
same logic applies for the week in the month.
if anyone did it already it would be grate.
(worst case I'll have to do it [:)])
thanks in advance,
Roy R.
if the 1st of Jan was a wednsday then only wed-sat are in the first week of the year, the next sunday which is the 5th day in the year is already on the second week in the year.
same logic applies for the week in the month.
if anyone did it already it would be grate.
(worst case I'll have to do it [:)])
thanks in advance,
Roy R.
Roy, you've discovered the reason Ralph Kimball is the god of datawarehousing. This is called the TIME dimension. You should create a series of tables in the data warehouse for these specific reasons. The DAY dimension table has attributes that define for each day it's characteristics, one of which is what you need. Go smack your architect and send him to TDWI and RKA for some training.
Good luck!
Kenneth Bland
Good luck!
Kenneth Bland
Roy,
There is a predefined routine that is close to what you are looking for. It's called weektag, located under routines/examples/functions. If you want to start your weeks with Sunday, then you will have to use your own code.
The basis of the routine is that the internal date format is the number of days elapsed since Dec 31 1967, and Jan 1 1968 was a Monday. Therefore internal date mod 7 will give you the day of the week (Sunday = 0). So 'MyDate' - Mod('MyDate', 7) will give you the date of the Sunday starting the week of 'MyDate'. With this date you can then use some logic and calculations to get the week number. It might take a little thought as to how to handle the first and last weeks of the year.
Hope this help,
Steve
There is a predefined routine that is close to what you are looking for. It's called weektag, located under routines/examples/functions. If you want to start your weeks with Sunday, then you will have to use your own code.
The basis of the routine is that the internal date format is the number of days elapsed since Dec 31 1967, and Jan 1 1968 was a Monday. Therefore internal date mod 7 will give you the day of the week (Sunday = 0). So 'MyDate' - Mod('MyDate', 7) will give you the date of the Sunday starting the week of 'MyDate'. With this date you can then use some logic and calculations to get the week number. It might take a little thought as to how to handle the first and last weeks of the year.
Hope this help,
Steve
Roy, if you are so inclined, I wrote a quick DS batch job you could run to generate a hash file of a TIME dimension. You could use this hash for reference lookup purposes to get the value you need for day of week. Better yet, expand this code to build a full DAY time dimension. You could pull from the hash file and put it into a static dimension table. Just paste this logic into a DS Batch job and run it. It should work as advertised, and you should set the ending and starting years. You can figure out the hash metadata from the comments. Use this in a job with preload to memory and it should be fairly economical.
Good luck!
OPEN "D_DAY" To F.D_DAY Else
EXECUTE "CREATE.FILE D_DAY DYNAMIC"
OPEN "D_DAY" To F.D_DAY Else
Call DSLogFatal("Unable to create hash file D_DAY", "MSG")
END
END
EXECUTE "CLEAR.FILE D_DAY" CAPTURING OUTPUT RETURNING MESSAGES
BeginYYYY = 2002
EndYYYY = 2004
For YYYY = BeginYYYY To EndYYYY
FirstDayOfYear = YYYY:"-01-01"
intFirstDayOfYear = ICONV(FirstDayOfYear,"D-YMD[4,2,2,]")
CurrentWeek = 1
intCurrentDate = intFirstDayOfYear
Results = ""
For day=0 to 366 UNTIL OCONV(intCurrentDate + 1, "DY") # YYYY
intCurrentDate = intFirstDayOfYear + day
DayArray = ""
DayArray = OCONV(intCurrentDate, "D-YMD[4,2,2]") ; * ISO date
DayArray = CurrentWeek ; * Week of the year
DayArray = OCONV(intCurrentDate, "DWA") ; * Day of the week
DayArray = OCONV(intCurrentDate, "DY") ; * Year
DayArray = OCONV(intCurrentDate, "DQ") ; * Quarter
DayArray = OCONV(intCurrentDate, "DJ")+0 ; * Julian date
If OCONV(intCurrentDate, "DW") = 6 Then CurrentWeek += 1 ; * Saturday is last day of week, but not in DS
SurrKey += 1
SmartKey = OCONV(intCurrentDate, "D-YMD[4,2,2]")
CONVERT "-" TO "" IN SmartKey
WRITE DayArray ON F.D_DAY, SmartKey
* Uncomment these lines for debugging to the job log
* CONVERT @AM TO "|" IN DayArray
* Results = DayArray
Next day
* Call DSLogInfo("Results for year [":YYYY:"]":@AM:Results, "MSG")
Next YYYY
Kenneth Bland
Good luck!
OPEN "D_DAY" To F.D_DAY Else
EXECUTE "CREATE.FILE D_DAY DYNAMIC"
OPEN "D_DAY" To F.D_DAY Else
Call DSLogFatal("Unable to create hash file D_DAY", "MSG")
END
END
EXECUTE "CLEAR.FILE D_DAY" CAPTURING OUTPUT RETURNING MESSAGES
BeginYYYY = 2002
EndYYYY = 2004
For YYYY = BeginYYYY To EndYYYY
FirstDayOfYear = YYYY:"-01-01"
intFirstDayOfYear = ICONV(FirstDayOfYear,"D-YMD[4,2,2,]")
CurrentWeek = 1
intCurrentDate = intFirstDayOfYear
Results = ""
For day=0 to 366 UNTIL OCONV(intCurrentDate + 1, "DY") # YYYY
intCurrentDate = intFirstDayOfYear + day
DayArray = ""
DayArray = OCONV(intCurrentDate, "D-YMD[4,2,2]") ; * ISO date
DayArray = CurrentWeek ; * Week of the year
DayArray = OCONV(intCurrentDate, "DWA") ; * Day of the week
DayArray = OCONV(intCurrentDate, "DY") ; * Year
DayArray = OCONV(intCurrentDate, "DQ") ; * Quarter
DayArray = OCONV(intCurrentDate, "DJ")+0 ; * Julian date
If OCONV(intCurrentDate, "DW") = 6 Then CurrentWeek += 1 ; * Saturday is last day of week, but not in DS
SurrKey += 1
SmartKey = OCONV(intCurrentDate, "D-YMD[4,2,2]")
CONVERT "-" TO "" IN SmartKey
WRITE DayArray ON F.D_DAY, SmartKey
* Uncomment these lines for debugging to the job log
* CONVERT @AM TO "|" IN DayArray
* Results = DayArray
Next day
* Call DSLogInfo("Results for year [":YYYY:"]":@AM:Results, "MSG")
Next YYYY
Kenneth Bland
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
A couple of points about Ken's code.
First, if you're using NLS and your TIME locale category specifies something other than Sunday as the first part of the week, then you will need to adjust his algorithm appropriately.
Second, since DataStage follows the convention of D_filename being the file dictionary for filename, I would have chosen a name that does not begin with "D_" for the hashed file in which to build the time dimension. In fact, I would probably have built it in a text file, ready for direct loading using a bulk loader.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
First, if you're using NLS and your TIME locale category specifies something other than Sunday as the first part of the week, then you will need to adjust his algorithm appropriately.
Second, since DataStage follows the convention of D_filename being the file dictionary for filename, I would have chosen a name that does not begin with "D_" for the hashed file in which to build the time dimension. In fact, I would probably have built it in a text file, ready for direct loading using a bulk loader.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
Ray's correct, I should have mentioned adjusting for the NLS issues. As I'm in America, I'm fairly shallow as to the worldly issues. Thanks Ray.
Ray also makes a good point about the poor choice of hash file. I'm used to calling dimension tables "D_" and fact tables "F_", so I just called the hash file that. My actual standard when created a hash file lookup based off a warehouse table is to prefix "stg_" or "skey_" before the hash name, so that this confusion doesn't come up. So, in my example code, think of the hash file name as "stg_D_DAY" to mean a DS staged copy of the D_DAY table from the warehouse. The "skey_D_DAY" table would be a natural key to surrogate key translator companion table. I try not to re-key "stg_" hash files by natural keys because those are ugly, that's the purpose of the "skey_" hash file. This is part of the methodology and conventions I helped Ascential design for their DS306 training course, so if anyone is interested that course is occassionally offered by Ascential.
Kenneth Bland
Ray also makes a good point about the poor choice of hash file. I'm used to calling dimension tables "D_" and fact tables "F_", so I just called the hash file that. My actual standard when created a hash file lookup based off a warehouse table is to prefix "stg_" or "skey_" before the hash name, so that this confusion doesn't come up. So, in my example code, think of the hash file name as "stg_D_DAY" to mean a DS staged copy of the D_DAY table from the warehouse. The "skey_D_DAY" table would be a natural key to surrogate key translator companion table. I try not to re-key "stg_" hash files by natural keys because those are ugly, that's the purpose of the "skey_" hash file. This is part of the methodology and conventions I helped Ascential design for their DS306 training course, so if anyone is interested that course is occassionally offered by Ascential.
Kenneth Bland