Board index MS SQL Server

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

 
 
 
Top

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

 
 
 
Top

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

 
 
 
Top

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

 
 
 
Top

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

 
 
 
Top

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

 
 
 
Top

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

 
 
 
Top

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

 
 
 
Top

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

 
 
 
Top

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

 
 
 
Top

1. Week : howto find the date of the first day of a week#

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.

2. Informatica usage with DB2 question

3. HELP: First Day in Week Setting and First Week in Year Help

4. What do I have to write to import a class??

5. Getting the first day of a calendar week (Date Format)

6. how do you display a parent field while scrolling through a child table?

7. set first day of week

8. Opeing database problem

9. set the first day of the week

10. How to set up the first week day for the DateDiff

11. first day of a week

12. Set first day of week

13. Weeks, first day on Monday


All times are UTC
Board index