## Date of the first day of the week

### Date of the first day of the week

Hello,
how can I calculate the date of the first day of the week in SQL ?
I know the week number in a year, and a year.

Regards
Jakub

--
_____________________________________________
Jakub Jablonski
Junior System Engineer
Ericpol Telecom sp. z o.o.
Targowa 9A, 90-042 Lodz, Poland

mobile:
tel: +48 42 6315520
fax: +48 42 6315555
http://www.ericpol.pl/
_____________________________________________

### Date of the first day of the week

You can use the following:

select dateadd (dd, -datepart (dw, getdate ()), getdate ())

In this case, you get the time part as well, since getdate() includes the time.  If you are using a column that is a datetime with no time component, then you also get the date with no time component, i.e.:

select dateadd (dd, -datepart (dw, Col1), Col1)
from MyTable

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCT
SQL Server MVP
Columnist, SQL Server Professional
www.pinnaclepublishing.com/sql
www.apress.com

Hello,
how can I calculate the date of the first day of the week in SQL ?
I know the week number in a year, and a year.

Regards
Jakub

--
_____________________________________________
Jakub Jablonski
Junior System Engineer
Ericpol Telecom sp. z o.o.
Targowa 9A, 90-042 Lodz, Poland

mobile:
tel: +48 42 6315520
fax: +48 42 6315555
http://www.ericpol.pl/
_____________________________________________

### Date of the first day of the week

The first day of the current week?  Based on a post by Mikhail:

SELECT CONVERT(CHAR(10),GETDATE()-diff,121)
FROM
(
SELECT diff = 0 UNION
SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6
) days
WHERE DATENAME(DW,GETDATE()-diff) = 'Sunday'

Just replace the weekday name (Sunday) with whatever you want to be the
first day of the week.

--
www.aspfaq.com / www.perfhound.com

> Hello,
> how can I calculate the date of the first day of the week in SQL ?
> I know the week number in a year, and a year.

> Regards
> Jakub

> --
> _____________________________________________
> Jakub Jablonski
> Junior System Engineer
> Ericpol Telecom sp. z o.o.
> Targowa 9A, 90-042 Lodz, Poland

> mobile:
> tel: +48 42 6315520
> fax: +48 42 6315555
> http://www.ericpol.pl/
> _____________________________________________

### Date of the first day of the week

Quote:> select dateadd (dd, -datepart (dw, getdate ()), getdate ())

This returns Saturday, 7/27 for me Tom... maybe you meant:

select dateadd (dd, 1-datepart (dw, getdate()), getdate())

--
www.aspfaq.com / www.perfhound.com

### Date of the first day of the week

Jakub,
Here is one of the ways of finding the beginning of a week (Monday's date)
for a given date.

begin

End

--To execute

exec WeekBeginning "20020730"

With a week number and year you should be able to calculate the date and
pass it to the above proc.

Cheers,
S.Kumar

> Hello,
> how can I calculate the date of the first day of the week in SQL ?
> I know the week number in a year, and a year.

> Regards
> Jakub

> --
> _____________________________________________
> Jakub Jablonski
> Junior System Engineer
> Ericpol Telecom sp. z o.o.
> Targowa 9A, 90-042 Lodz, Poland

> mobile:
> tel: +48 42 6315520
> fax: +48 42 6315555
> http://www.ericpol.pl/
> _____________________________________________

### Date of the first day of the week

Yup, Aaron.  I was looking at my day planner and it doesn't start on Sunday.  Duh.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCT
SQL Server MVP
Columnist, SQL Server Professional
www.pinnaclepublishing.com/sql
www.apress.com

> select dateadd (dd, -datepart (dw, getdate ()), getdate ())

This returns Saturday, 7/27 for me Tom... maybe you meant:

select dateadd (dd, 1-datepart (dw, getdate()), getdate())

--
www.aspfaq.com / www.perfhound.com

### Date of the first day of the week

Thanks
Works perfect, but I don't know the date, I only know WEEK.
Now we have week number 31 in a year number 2002. I need to get the date
'20020729'.
I can get week number from a date, but I need to get a date from the
week number (for example date of the first day, or last day, or whichever).

--
_____________________________________________
Jakub Jablonski
Junior System Engineer
Ericpol Telecom sp. z o.o.
Targowa 9A, 90-042 Lodz, Poland

mobile:
tel: +48 42 6315520
fax: +48 42 6315555
http://www.ericpol.pl/
_____________________________________________

### Date of the first day of the week

My first stab is this:

However it looks like it puts you a week too far (likely because the year
started on a Tuesday?)

--
www.aspfaq.com / www.perfhound.com

> Thanks
> Works perfect, but I don't know the date, I only know WEEK.
> Now we have week number 31 in a year number 2002. I need to get the date
> '20020729'.
> I can get week number from a date, but I need to get a date from the
> week number (for example date of the first day, or last day, or
whichever).

> --
> _____________________________________________
> Jakub Jablonski
> Junior System Engineer
> Ericpol Telecom sp. z o.o.
> Targowa 9A, 90-042 Lodz, Poland

> mobile:
> tel: +48 42 6315520
> fax: +48 42 6315555
> http://www.ericpol.pl/
> _____________________________________________

### Date of the first day of the week

Thanks, it does the trick.

--
_____________________________________________
Jakub Jablonski
Junior System Engineer
Ericpol Telecom sp. z o.o.
Targowa 9A, 90-042 Lodz, Poland

mobile:
tel: +48 42 6315520
fax: +48 42 6315555
http://www.ericpol.pl/
_____________________________________________

### Date of the first day of the week

AMB

> Hello,
> how can I calculate the date of the first day of the week in SQL ?
> I know the week number in a year, and a year.

> Regards
> Jakub

> --
> _____________________________________________
> Jakub Jablonski
> Junior System Engineer
> Ericpol Telecom sp. z o.o.
> Targowa 9A, 90-042 Lodz, Poland

> mobile:
> tel: +48 42 6315520
> fax: +48 42 6315555
> http://www.ericpol.pl/
> _____________________________________________

Hi everybody !

Does anybody has to do it ?

I had to know the first of a week number nweek ?

What is the best solution, because it is in a loop (scan for ...) of a
big DBF.

Fred.