convert week and year into datetime

pucklinger
pucklinger used Ask the Experts™
on
Hi,

how can I convert the week and the year in a datetime?
(I need it in a condition)

for example:

week=2
year= 2003

wanted result: datetime='06/01/2003' (or similar)

convert and cast seems only to work with month?!?

Thanks for help
PU
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

This example seems to do the trick:

-- Make sure we use Monday as start of week
SET DATEFIRST 1

DECLARE @pintWeek INTEGER
DECLARE @pintYear INTEGER
SET @pintWeek = 2
SET @pintYear = 2003

DECLARE @pdatDate DATETIME
-- Get the first day of the defined year
SET @pdatDate = CAST(@pintYear AS VARCHAR) + '-01-01 00:00:00.000'

SELECT DATEADD(dd, - (DATEPART(dw, DATEADD(wk, @pintWeek - 1, @pdatDate)) -1), DATEADD(wk, @pintWeek - 1, @pdatDate))


Cheers

Author

Commented:
Hi,

yes, this solves the problem, but I am working with a third party product, and so I have no chance for such an impact.

I would need a simple function like:
select datetime(week='20', year='2002') ....
(if exists :-) )

funny thing:
select cast ( 'August 2003' as datetime)
works fine.
The result is '2003-08-01 00:00:00.000',
but I think this only works because August is specific for a month....

select cast ( '08 2003' as datetime)
generates an error!

Thanks
PU
I don't believe there is a simple function like that to do what you want. It's the calculating of the 1st day of the specified week that is the problem and makes the solution I provided look a bit more complicated.

CAST(<Value> AS DATETIME), expects a string representation of a valid date which could be in one of many different formats (e.g. dd/mm/yyyy, MMM YYYY etc etc). What you tried passing in (ww yyyy) is not a valid format for passing a value in.

If you didn't need to get the 1st day of a week, it would be much simpler:

SELECT DATEADD(wk, <WeekNumber> - 1, '2003-01-01 00:00:00.000')

This would return the date in the <WeekNumber> week of 2003, but it would probably not be the start of the <WeekNumber> week.

Cheers
this will give you the first day of the week

the only thing with this is if you put week 1 then you will get 12/28/2002 - I don't know if that has a big impact on your query or not...

declare @weekno int, @year int
set @weekno =3
set @year = 2003
select dateadd(d,((@weekno-1)*7) - (datepart(dw,convert(varchar,@year)+'/1/1')-1),convert(varchar,@year)+'/1/1')

good luck

Author

Commented:
Thanx a lot!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial