CODEWITHSUNDEEP
sqlsql-servert-sql
Kulis
Kulis

Reputation: 1010

Convert week date to calendar date

I have column with dates in format

year/number_of_week_in_year/number_of_day_of_the_week, for example:

2015015 = 01.01.2015

How to write query which convert this date to RRRRmmdd format?

Upvotes: 0

Views: 147

Answers (4)

Michael McMullin
Michael McMullin

Reputation: 1470

Here's a simple solution I threw together, probably not the cleverest way to do it but hopefully makes sense:

DECLARE @inDate CHAR(7),
        @inYear CHAR(4),
        @inWeek INT,
        @inDay  INT,
        @OutDate DATETIME;

SET @inDate = '2015015';
SET @inYear = SUBSTRING(@inDate, 0, 5);
SET @inWeek = CAST(SUBSTRING(@inDate, 5, 2) AS INT) - 1 -- Reduce by 1 because it will be added to start of year
SET @inDay  = CAST(SUBSTRING(@inDate, 7, 1) AS INT)

SET @OutDate = CAST(@inYear + '-01-01' AS DATETIME)
SET @OutDate = DATEADD(dd, -DATEPART(weekday, @OutDate) + @inDay, @OutDate)
SET @OutDate = DATEADD(ww, @inWeek, @OutDate)

PRINT @OutDate -- Gives Jan 1 2015

Upvotes: 2

Rafał Wojtaszek
Rafał Wojtaszek

Reputation: 668

You can create simple function to do this:

CREATE FUNCTION dbo.GetDate ( @Date AS NVARCHAR(7) )
RETURNS DATE
AS
BEGIN
    DECLARE @WeekDayOfYearStart INT ,
        @WeekNum INT ,
        @DayInWeek INT ,
        @DateOut DATE;

    SELECT  @WeekNum = CONVERT(INT, RIGHT(LEFT(@Date, 6), 2)); 

    SELECT  @DayInWeek = CONVERT(INT, RIGHT(@Date, LEN(@Date) - 6));

    SELECT  @WeekDayOfYearStart = DATEPART(WEEKDAY,
                                           LEFT(@Date, 4) + '0101');

    SELECT  @DateOut = DATEADD(DAY,
                               ( @WeekNum - 1 ) * 7 + @DayInWeek
                               - @WeekDayOfYearStart,
                               LEFT(@Date, 4) + '0101');
    RETURN  @DateOut;
END;
GO

Upvotes: 1

w33
w33

Reputation: 36

If I understand right the input is varchar(10). You can try this

DECLARE @d varchar(10)
set @d='2015/12/11'
select 
    SUBSTRING(@d, 6, charindex('/', @d, 6) - 6) as week,
    SUBSTRING(@d, charindex('/', @d, 6)+1, charindex('/', @d, len(@d) -     charindex('/', @d, 6))) as day,
DateAdd(
    day,
    cast(SUBSTRING(@d, charindex('/', @d, 6)+1, charindex('/', @d, len(@d) - charindex('/', @d, 6))) as int),
    DateADD(
        week, 
        cast(SUBSTRING(@d, 6, charindex('/', @d, 6) - 6) as int), 
        DATEFROMPARTS(cast(SUBSTRING(@d, 0, 5) as int), 1,1)
    )
) as d

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

Here's an untested answer for you, as I don't have access to SQL Server to fiddle around (on my phone at the moment).

The easy part is to get at DATE value for the year...

DATEADD(year, (input / 1000) - 1900), 0)

Then you need to add a certain number of days to it...
- 7 days for each week (not including week 1)
- 1 day of each day of the week 

((input / 10) % 100 - 1) * 7
+ input % 10

Then deducting a number of days depending on the day of the week that year started on.

DATEPART(weekday, <your year as a date>)


Which seems to give...

DATEADD(
    day,
    ((input / 10) % 100 - 1) * 7
    + input % 10
    - DATEPART(weekday, DATEADD(year, (input / 1000) - 1900, 0)),
    DATEADD(year, (input / 1000) - 1900, 0)
)


Using your example...

DATEADD(
    day,
    ((2015015 / 10) % 100 - 1) * 7
    + 2015015 % 10
    - DATEPART(weekday, DATEADD(year, (2015015 / 1000) - 1900, 0)),
    DATEADD(year, (2015015 / 1000) - 1900), 0)
)

=>

DATEADD(
    day,
    (01 - 1) * 7
    + 5
    - DATEPART(weekday, DATEADD(year, 2015 - 1900, 0)),
    DATEADD(year, 2015 - 1900, 0)
)

=>

DATEADD(
    day,
    0 * 7
    + 5
    - DATEPART(weekday, '2015-01-01'),
    '2015-01-01'
)

=>

DATEADD(
    day,
    0 * 7
    + 5
    - 5,
   '2015-01-01'
)

=>

'2015-01-01'

Upvotes: 2

Related Questions