Date of the first day of the week

Date of the first day of the week

Post by Jakub Jablonsk » Wed, 31 Jul 2002 21:34:33



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

Post by Tom Morea » Wed, 31 Jul 2002 21:42:17


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
Toronto, ON Canada
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

Post by Aaron Bertrand [MVP » Wed, 31 Jul 2002 21:49:46


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

Post by Aaron Bertrand [MVP » Wed, 31 Jul 2002 21:53:50


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

Post by Suresh Kuma » Wed, 31 Jul 2002 22:02:54


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

Post by Tom Morea » Wed, 31 Jul 2002 22:00:46


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
Toronto, ON Canada
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

Post by Jakub Jablonsk » Wed, 31 Jul 2002 22:18:28


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

Post by Aaron Bertrand [MVP » Wed, 31 Jul 2002 22:32:49


My first stab is this:

SELECT dateadd(ww, 31, '20020101')

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

Post by Jakub Jablonsk » Wed, 31 Jul 2002 23:00:35


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

Post by Alejandro Mes » Wed, 31 Jul 2002 23:01:33











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/
> _____________________________________________