# Calculate Week From Date

Jun 22, 2004

Hi,

I want to convert a date to a weeknumber in my view.

How is this possible with SQL?

## SQL Server 2008 :: Get Week Ending Date Given Week Number

Apr 9, 2013

How can I get Saturday's date given the week number?

This is my week number, SELECT DATEPART(WEEK, DATEADD(MONTH, +3, ApptDt2)). I need to get Saturday's date from this week.

## Convert Week Number To Date Of 1st Day Of That Week...

Feb 2, 2008

I'm looking for a way to convert the [week number, year] back to the date for the 1st day of that week.

Example (assuming DATEFIRST has been set to 1) : Week 3, 2008 = January 14, 2008

Is there a SQL function that will do this?

Thanks,
Mike

## T-SQL (SS2K8) :: Calculate And Display Week Between Two Dates

Nov 4, 2015

I want to display week between two dates as below.

requirement is as:

suppose there are two dates.(which will comes dynamically, so no. of weeks varied)
10/20/2015 and 01/01/2016

Now between this two dates, i want to calculate number of weeks on another date which is coming from table.

Say for example the column date is coming as 10/23/2015 then it will fall in week-1

Same way if 11/01/2015 falls in week2.

Jun 6, 2006

seriously now

## SQL Server 2012 :: Calculate Number Of Days Based On Computer System Date And Due Date Row

Mar 18, 2014

I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.

Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.

## Date Of First Day In Week

Apr 27, 2005

I need to write a user defined function that will return the date of the first day of a week when provided with the week number. I had an idea of using a while loop with that will keep adding 1 day from January 1st until the week number (found via the DATEPART function) is equal to the supplied week number. Not sure if this is the best way though - any ideas?

Donald.

## Date Of First Day Of Week

May 26, 2008

I'm wanting to see if there is a simpler way to accomplish the retrival of the first day of the week Date. What I currently have is:

CASE
WHEN DATEPART(DW, GETDATE()) = 1 THEN DATEADD([D], - 7, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 2 THEN DATEADD([D], - 8, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 3 THEN DATEADD([D], - 9, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 4 THEN DATEADD([D], - 10, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 5 THEN DATEADD([D], - 11, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 6 THEN DATEADD([D], - 12, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 7 THEN DATEADD([D], - 13, GETDATE()) END AS StartWeek1

I'm trying to figure if there might be an easier way to code this. Thanks.

## Next Date Of Particular Week Day

Apr 6, 2008

Hi,

How would it be possible to calculate the next date of a given week day.

So if today is 6th April 2008 find the date of the next wednesday (i.e. the up and coming wednesday) of the next saturday etc.

You help is appreciated.

Cheers

## Convert A Date To A Day Of The Week.

Nov 15, 2007

Hey, Im pretty sure this is possible, but let me know if it isnt.I have a table of dates in the format "DD/MM/YYYY", is there a way of converting it to output the day of the week? for example 16/11/2007 would display Friday insted.Thanks in advance John

## Date Week Question

Aug 21, 2002

If I use Datepart(wk, getdate()) to get the current week, is there a way to get the date for the first day of the week that is returned?

I have a query that I group everything by week number, but would like to show the actual date for the beginning of the week.

Any help or guidance would be appreciated....

## Get Last Day Of Week (Saturday) From Any Date?

Nov 12, 2003

Greetings,

Any tips on using datediff to get last dat of week( in this case saturday) from a date input in SQL?

Thanks for any help!

## Select Date A Week Ago

Oct 4, 2006

How to select a date a week ago?
Below is something i want

10/04/2006 ->09/27/2006

my code is like below:
SELECT CONVERT(CHAR(10),GETDATE(),101) - day(7)

but it cant work.

## Date Time -one Week

Dec 1, 2005

Hi

I need help with a query that returns todays date -one week
Ex: the 1 december it should return "24" for 24 november.

this one below works only from the 7th every month else it give me a negative number :)

SELECT RIGHT ('0' + CAST(DAY(GETDATE()) - 7 AS varchar); 2)

//Mr

## How Can I Get The Date From Week Number

Jan 17, 2008

I have week number and i want to get the date from that
for example Week=52 will be the first day of that day which is monday 12/24/2007

Thanks

## Getting The Date From Week Number

Oct 7, 2007

I have a column in my dataset that contains the week number.

I would like to use 'Expression' Or code function to do that
how can i get the date from week number? for example the week number is 38

so i wil get this output: 09/17/07 - 09/23/07 (the week starts on Monday and ends on Sunday)
Thanks

## Week/Month Date Range

Nov 19, 2001

I need SQL to determine what the date range for the previous week was, ie
Start date 11/11/01 for Last Sunday through 11/17/01 the following Saturday.
The results will populate a drop down box for user queries.
Thanx.

## Find The Date If I Know The Week And Year

Aug 5, 2004

Hi Guys,
Can anyone please let me know How to find the first day of the week, if I know the weekno and the year.
For ex: If I know the week no is 22 and Year is 2003 then I should find a way to output the date. which is the first day (monday) of that week
for the above query the date will be eg:26/06/2003

Thanks:confused: Find the Date if I know the week and year

## Converting Year/week/day To Date

Mar 19, 2007

i have this SQL table with a year, week and day-of-week field, but no field containing the actual date

is there a way (e.g. in a view of the SQL table) to transform the year/week/day combination into a date string (e.g. format dd/mm/yyyy) ?

## Automate Week Ending Date

Nov 3, 2005

How do i automate, by stored procedure, generating the "week ending" date using a field data.

------------------------------------------------------------

CREATE TABLE [dbo].[t_Work_Hours] (
[WorkHoursID_PK] [int] IDENTITY (1, 1) NOT NULL ,
[PeopleID_FK] [int] NOT NULL ,
[JobID_FK] [int] NULL ,
[StartTime] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[Title] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WeekEnding] [datetime] NULL ,
CONSTRAINT [PK_t_Work_Hours] PRIMARY KEY CLUSTERED
(
[WorkHoursID_PK]
) ON [PRIMARY] ,
CONSTRAINT [FK_t_Work_Hours_t_Work_People] FOREIGN KEY
(
[PeopleID_FK]
) REFERENCES [dbo].[t_Work_People] (
[PeopleID_PK]
)
) ON [PRIMARY]
GO
-------------------------------------------------------

What i have tried has'nt even been close to a solution.
UPDATE dbo.t_Work_Hours
SET WeekEnding = DATETIME ( ??? , StartTime) . I have no idea where to go!

----------------------------------------------------------------
WorkHoursID_PK, PeopleID_FK, JobID_FK, StartTime, EndTime, Title, WeekEnding
7, 40, 3, 11/1/2005 6:00:00 AM, 11/1/2005 4:30:00 PM, J, (NULL)
8, 43, 3, 10/31/2005 6:00:00 AM, 10/31/2005 4:30:00 PM, F, 11/6/2005 11:59:00 PM
9, 43, 3, 11/1/2005 6:00:00 AM, 11/1/2005 4:30:00 PM, F, 11/6/2005 11:59:00 PM
-----------------------------------------------------------

## Start Date Of The Current Week

Feb 17, 2006

Can anyone explain the result of the sql statement

Can we use this to get the start date of the current week always???

## SQL 2012 :: Extracting Week-day Name From Date?

Mar 7, 2014

I want to extract day of the week from given date.

If the given date is 07-03-2014(dd-mm-yyyy).IF i run the query the output should be =Friday

## Get Date From Week Number In A Year

Feb 11, 2014

Can I get a date from a week number in a certain year, like I know it's week 7 in 2014 (I may know the month.)

## Convert Date To Week Number

Apr 30, 2015

I want to convert date to week consider saturday is first day of week and friday is end of week. for input is orderdate and output should be week. example orderdate 11.04.15 to 17.04.15 week should be 16. orderdate 18.04.15 week should change to 17.

Order DateWeek
11-Apr-201516
12-Apr-201516
13-Apr-201516
14-Apr-201516
15-Apr-201516
16-Apr-201516
17-Apr-201516

Order DateWeek
18-Apr-201517

## Date Part Returns Too May Week 53

Apr 8, 2008

There should be only 52 weeks in a year unless there is a leep week(these should not happen on back to back years).
What is going wrong?

SET DATEFIRST 7

select DatePart(Week, 'Dec 29, 1998'), 'Dec 29, 1998'

union

select DatePart(Week, 'Dec 27, 1999'), 'Dec 27, 1999'

union

select DatePart(Week, 'Dec 24, 2000'), 'Dec 24, 2000'

week of year
date

53
24-Dec-00

53
27-Dec-99

53
29-Dec-98

In my database the following dates have all been assigned to week 53

Year
Month
Day

1985
12
30

1985
12
31

1986
12
30

1986
12
31

1987
12
28

1987
12
29

1987
12
30

1987
12
31

1988
12
27

1988
12
28

1988
12
29

1988
12
30

1989
12
31

1990
12
31

1991
12
30

1991
12
31

1992
12
28

1992
12
29

1992
12
30

1993
12
27

1993
12
28

1993
12
29

1993
12
30

1994
12
27

1994
12
28

## Date Range For Previous Week

Sep 17, 2007

How do i get only the records for everything with only the dates from last week (monday-friday)? I want to have this run every week for the previous week. Here's my stored procudure. Right now i have this to run for the day before, but now i need it for the week before. So its a range. Please Help. Thanks!

Code Snippet
CREATE PROCEDURE [dbo].[Testing_Out_Of_Stock_SKUS_WEEKLY]
(@Classification varchar(50))
AS
BEGIN
SELECT RC_STAT.dbo.Brand_Dimension.Report_Level,
RC_STAT.dbo.Brand_Dimension.[Cat vs Dog],
RC_STAT.dbo.Brand_Dimension.Item_Merged,
Qry_Sales_Group.Region_Key,
Qry_Sales_Group.Region,
Qry_Out_Of_Stock.product_structure_level,
Qry_Out_Of_Stock.product_entity_code,
Qry_Out_Of_Stock.cycle_day,
Qry_Out_Of_Stock.customer_code,
Qry_Out_Of_Stock.description,
Qry_Sales_Group.Code,
Qry_Sales_Group.SR_Name,
Qry_Sales_Group.Name AS Territory_Name,
Qry_Out_Of_Stock.Store_Name,
Qry_Out_Of_Stock.time_log,
Qry_Out_Of_Stock.out_of_stock,
Period_Code
FROM RC_STAT.dbo.Brand_Dimension INNER JOIN
dbo.Qry_Out_Of_Stock ON
RC_STAT.dbo.Brand_Dimension.Item_Key = dbo.Qry_Out_Of_Stock.product_entity_code COLLATE SQL_Latin1_General_CP1_CI_AS INNER JOIN
dbo.Qry_Sales_Group ON
dbo.Qry_Out_Of_Stock.sales_person_code = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS
Where Classification=@Classification DateDiff(dd,0,dbo.Qry_Out_Of_Stock.time_log) =
case
when Datepart(Weekday, Getdate()) = 2 then datediff(dd,0,GetDate()) - 3
else datediff(dd,0,GetDate()) - 1
end

END
SET NOCOUNT OFF

## Convert Date Range Into Week Days

Nov 3, 2006

Hi,

I don't know if this can be done, but hopefully one of the experts in this forum cna help me out.

I have a TimeOff table which contains 5 fields: timeoffID, employeename, startdate, enddate, and timofftype. I need to create a report which shows how many people take sick day (one of the timeofftypes) on each week day (i.e. Monday, Tuesday, Wednesday, and etc.) The problem is that I don't know how to convert the date range (from startdate to enddate) into indvidual week day for each timeoff record.

For example, on the table,

TimeoffID employeename startdate enddate timeofftype
1 Andy 11/02/2006 11/03/2006 Sick Day
2 Bill 11/03/2006 11/03/2006 Sick Day

The report needs to show:

Monday Tuesday Wednesday Thursday Friday Saturday Sunday
0 0 0 1 2 0 0

## Convert Week Number Back To First Date

Feb 28, 2008

hi guys,
How could I convert 'week 9 2008' back into the first day of that week ?

is that even possible ?

## Add Date Range By Week To Existing SQL Statement

Mar 31, 2008

Hello all,

I have the following SQL statement that works great for my purpose:

SELECT DATEADD(hh, DATEPART(hh, Date), DATEADD(d, DATEDIFF(d, 0, Date), 0)) AS TimePeriod, AVG(ZNT) AS Expr1, AVG(SAT) AS Expr2, AVG(RAT) AS Expr3,
AVG(RH) AS Expr4
FROM HVACValues
ORDER BY TimePeriod

I'd like to do everything above but add to display only those records from 1/1/07 to 1/7/07...so a week's worth.

I'm no SQL expert but I assume that I'd add 'WHERE TimePeriod = ....' Any help is much appreciated!

Thank you very much.

## Rolling Weeks Date Range Of Current Week

Apr 22, 2015

I require some scripting to return data which sits in a rolling weeks date range of the current week.

For example, on a Monday I want to return data from the previous day (Sunday to the previous Monday).
On a Tuesday I want to return data from the previous day (Monday to the up coming Sunday).
On a Wednesday I want to return data from the Monday (previous 2 days ago to the upcoming Sunday)
On a Thursday I want to return data from the Monday (previous 3 days ago to the upcoming Sunday)

And so forth.

So I have my SQL script which works with static, fixed date ranges. But I need this additional dynamic date range logic built in.

## Transact SQL :: Date Calculation - 7 Days Prior To Specified Day Of Last Week

Sep 8, 2015

I need to run a select on Mondays to pull data for 7 days prior to the Thursday of last week; i.e. Friday - Thursday inclusive.  I'm sure this is simple, but I work with dates so infrequently that I need a refressher.

## How To Get The Week Number Of A Specific Date In SQL Server 2005

Jan 10, 2008

Hi,

Can anybody tell me to How to get the week number of a specific Date in SQL Server 2005.

Like : say Date "03/01/2008" belongs to the 1st week of the year 2008, same way "21/02/2008" is 8th week of the year 2008.

So, the same way I need to know the week number of a given (variable) date.

I need to write / use a Function in SQL server which will return the Number (INT) (i.e. 1 or 8 or 10 etc...) against a specific (Parameter) date.

regards,

Saibal

Note : Please feel free to write me if my question is not clear.

## Calculate Date

Nov 12, 1999

Hi All!
I need a query to find all dates from today to one-year back.
If I start from today day I need find all dates until 11/12/98.
Thanks a lot.
Greg.