How to convert a week (200851) into a date (2008-12-27)?

Question

How could I convert a year-week (eg 0852 or 200852) into a date (eg 2008-12-31 or specifically a week-ending day ie Saturday 2008-12-27 or a week-beginning day ie 2008-12-21) ? Any day of the week-ending will do, Friday, Saturday, Sunday or Monday.

Answer 1

In Oracle:

SELECT  TO_DATE('04.01.' || SUBSTR(iso, 1, 4)) + (TO_NUMBER(SUBSTR(iso, 5, 2))) * 7 - TO_CHAR(TO_DATE('04.01.' || SUBSTR(iso, 1, 4)), 'D')
FROM    (
    SELECT '200101' AS iso
    FROM dual
    )

Answer 2

If this is in Oracle, check out the TO_Date function with various Options, YYYYWW. select TO_Date ('200852' YYYYWW) from dual;

http://www.oradev.com/oracle_date_format.jsp Once you get the begning of the week, you can always add number of days to get weekend date.

Answer 3

Date functions are pretty much database-specific. In MySQL for example, you could do the following to get the monday of the week in 'YYYYWW' format:

select date_sub(makedate(left('YYYYWW', 4), 1 + (right('YYYYWW', 2) - 1) * 7), interval weekday(makedate(left('YYYYWW', 4), 1 + (right('YYYYWW', 2) - 1) * 7)) DAY);

With makedate(left('YYYYWW', 4), 1 + (right('YYYYWW', 2) - 1) * 7) you would get a date which is definitely in the given week and year, and with the date_sub function we go back to the monday of that week.

Examples:

• for '200906' this would return '2009-02-02
• for '200901' it would return '2008-12-29' (this is in fact the Monday of week 1 of 2009)

Answer 4

Hi, thanks for your help and sorry, I did not realise that so many functions are database specific. I work with T-SQL and will now add the T-SQL tag.

Answer 5

MS SQL SERVER has DATEADD functionallity that should help...

DECLARE @date_string NCHAR(6)
SELECT  @date_string = N'200852'

SELECT DATEADD(
           WEEK,
           CAST(RIGHT(@date_string, 2) AS INT),
           DATEADD(
               YEAR,
               CAST(LEFT(@date_string, 4) AS INT) - 1900,
               0
           )
       )

EDIT:

Sorry, missed the bit about making it a saturday, etc...

Once you have the value, use DATEPART to get what day of the week it is, and subtract that from your answer...

DECLARE @new_date DATETIME
SELECT  @new_date = '2008 Dec 30'

SELECT DATEADD(DAY, 1-DATEPART(dw, @new_date), @new_date)

This will bring the value to the start of the week, depending on what you have set DATEFIRST to.

Answer 6

Here's a solution that's almost database independent, if that's important.

Create a table called ALMANAC, primary key DATE, that lists all the attributes of a date that you plan on using. One of them could be YEAR, another could be WEEK. You can include whatever computable functions of DATE you want to use a lot. You could also include company specific DATE attributes, like whether the date is a company holiday.

Write one program to populate DATE for say 10 years (about 3650 rows plus some leap years). This program will use the environment specific date functions available to you either in the DBMS of in some programming language. Once you've populated the ALMANAC table, use it like ordinary data. Join the primary key with any Date fields in your database.

This turns out to be enormously useful for doing such things as preparing a report by week, by month, by quarter, and by year, almost automatically.

If DATE isn't fine enough granularity, you can even divide the Almanac into smaller units of time, like shifts where 3 shifts is one day.

Answer 7

Thanx a million Dems and Walter! Greg