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.

View 8 Replies


ADVERTISEMENT

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.

View 9 Replies View Related

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

View 11 Replies View Related

Date Of First Day Of Week For Current Week

Jun 6, 2006

seriously now

View 1 Replies View Related

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.

View 2 Replies View Related

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

View 6 Replies View Related

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 

View 3 Replies View Related

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?

View 2 Replies View Related

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....

View 1 Replies View Related

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!

View 3 Replies View Related

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.

View 2 Replies View Related

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)


Thx in advance


//Mr

View 4 Replies View Related

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

View 9 Replies View Related

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

View 10 Replies View Related

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.

View 1 Replies View Related

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

I really appreciate your Help

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

View 3 Replies View Related

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) ?

View 7 Replies View Related

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

View 3 Replies View Related

Start Date Of The Current Week

Feb 17, 2006

Can anyone explain the result of the sql statement

Select DATEADD(wk, DATEDIFF(wk, 6,getdate()), 6)


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

View 2 Replies View Related

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

View 2 Replies View Related

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.)

View 7 Replies View Related

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

View 3 Replies View Related

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

View 4 Replies View Related

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

View 5 Replies View Related

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


Thank you in advance!

View 4 Replies View Related

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 ?

View 5 Replies View Related

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
GROUP BY DATEADD(hh, DATEPART(hh, Date), DATEADD(d, DATEDIFF(d, 0, Date), 0))
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.

View 3 Replies View Related

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.

View 1 Replies View Related

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. 

View 7 Replies View Related

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.

View 5 Replies View Related

Power Pivot :: Cumulative Total For Current Week Days And Previous Week

Nov 30, 2015

We are trying to compare our current calendar week (based on Monday being the first day of the week) with the previous calendar week. 

I'm trying to produce a line chart with 2 axis:

- x axis; the day of the week (Mon, Tues, Wed etc - it is fine for this to be a # rather than text e.g. 1 = Mon, 2 = Tues etc)
- y axis; the cumulative number of orders 

The chart needs two series:

Previous Week. The running count of orders placed that week. 
Current Week. The running count of orders placed this week. 

Obviously in such a chart the 'Current Week' series is going not going to have values along the whole axis until the end of the week. This is expected and the aim of the chart is to see the current week compares against the previous week for the same day. 

I have two tables:

Orders TableCalendar Table

The calendar table's main date column is [calDate] and there are columns for the usual [calWeekNum], [calMonth] etc. 

My measure for counting orders is simply; # Orders: = countrows[orders].

How do I take this measure and then work out my two series. I have tried numerous things such as adapting TOTALMTD(), following articles such as these:

- [URL] ...
- [URL] ...

But I have had no luck. The standard cumulative formulas do work e.g. if I wanted a MTD or YTD table I would be ok, it's just adjusting to a WTD that is causing me big issues.

View 3 Replies View Related

Power Pivot :: Get Last Available Value For Measure In Current Context (week / Month Level And Not Date)

Jul 21, 2015

I can't seem to upload images so here's the link: [URL] ....

I need to get the last value of # Orders week avg in the current context (the highlighted value). The calendar dimension here is a week level one so I don't think I can use LASTNONBLANK. The measure is from the facts_sales table which is linked to a calendar dimension via a date key. 

View 8 Replies View Related

Pivot That Increases Columns Dynamically With Column Header As Boundary Date For Each Week

Dec 5, 2007

Hi All,

The current/ Base table would be like below,





Products

level

Date


N1

b

11/5/2007


N2

p

11/6/2007


N3

p

11/7/2007


N4

p

11/14/2007


N5

b

11/15/2007


N6

p

11/23/2007




Expected Result.







<=11/7/2007

<= 11/14/2007

<=11/21/2007


b

1

1

2


p

2

3

4


Total

3

4

6




As you can see, the above table has cumulative data.
1. It calculates the number of Products submitted till a particular date- weekly
2. The date columns should increase dynamically(if the dates in base table increases) each time the query is executed
For ex: the next date would be 11/28/2007
I tried something like, it gives me count of €˜b€™ level and €˜p€™ level products by week
declare @date1 as datetime
select @date1 = '6/30/2007'
while (@date1 != (select max(SDate) from dbo.TrendTable))
begin
set @date1 = @date1 + 7
select Level, count(Products)
from
dbo.TrendTable
where SDate < @date1
group by Level
end
what I think is required is a pivot that dynamically adds the columns for increase in date range.
/Pls suggest if any other way of achieving it.
Pls help!!!

Thanks & Regards

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved